From: Jesper K. Pedersen <jkp@solnet.homeip.net>
To: pgsql-sql@postgresql.org
Subject: Re: PLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly
Date: Sat, 27 May 2006 11:28:44 +0200
Message-ID: <44781BCC.7000607@solnet.homeip.net> (raw)
In-Reply-To: <20060526105037.GB5492@phlogiston.dyndns.org>
References: <20060526101207.5C4B35B0B83@svr4.postgresql.org>
<20060526105037.GB5492@phlogiston.dyndns.org>
<!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.GB5492@phlogiston.dyndns.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>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jkp@solnet.homeip.net
Subject: Re: PLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly
In-Reply-To: <44781BCC.7000607@solnet.homeip.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox