Re: PLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly

From: "Jesper K(dot) Pedersen" <jkp(at)solnet(dot)homeip(dot)net>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: PLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly
Date: 2006-05-27 09:28:44
Message-ID: 44781BCC.7000607@solnet.homeip.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Andrew Sullivan wrote:
<blockquote cite="mid20060526105037(dot)GB5492(at)phlogiston(dot)dyndns(dot)org"
type="cite">
<pre wrap="">On Fri, May 26, 2006 at 05:11:26PM +0700, andi wrote:
</pre>
<blockquote type="cite">
<pre wrap="">select rank() over(order by testeridpk ) as rank , * from tester;

I get the result is like this,

RANK TESTERIDPK TESTER_NAME

1 10 TESSS

2 90 NAMAAA

3 100 UUUUUUUU

How in postgres sql I get the same result , please help me, because iam
really frustating with this duty.
</pre>
</blockquote>
<pre wrap=""><!---->
There's no built in for that that I know of. You could use a
temporary sequence to do it:

BEGIN;
CREATE SEQUENCE tempseq;
SELECT nextval('tempseq') as rank, testeridpk, tester_name FROM testers
ORDER BY testeridpk;
ROLLBACK;

which, I _think_, will get you what you want (i.e. that's not
tested). The ROLLBACK is just there to clean up the sequence.
</pre>
</blockquote>
You can not rely on the "order by" to sort your date at fetch time, it
will read the date adding the nextval(...) at read time and then sort
it.<br>
<br>
Best regards<br>
Jesper K. Pedersen<br>
<br>
</body>
</html>

Attachment Content-Type Size
unknown_filename text/html 1.4 KB

In response to

Browse pgsql-sql by date

  From Date Subject
Next Message AKHILESH GUPTA 2006-05-29 01:28:39 Free 1000 Rupee bank note
Previous Message Merlin Moncure 2006-05-26 14:53:38 Re: [SQL] (Ab)Using schemas and inheritance