X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id 93C4F9FA2FA
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 27 May 2006 06:46:54 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 38163-03
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 27 May 2006 06:46:47 -0300 (ADT)
X-Greylist: delayed 00:17:36.966786 by SQLgrey-
Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159])
	by postgresql.org (Postfix) with ESMTP id 33E9E9F9EEB
	for <pgsql-sql@postgresql.org>; Sat, 27 May 2006 06:46:48 -0300 (ADT)
Received: from rhea.solnet (0x50c60afe.vgnxx3.adsl-dhcp.tele.dk
	[80.198.10.254])
	by svr4.postgresql.org (Postfix) with ESMTP id 2BE5E5B318E
	for <pgsql-sql@postgresql.org>; Sat, 27 May 2006 09:29:09 +0000 (GMT)
Received: from [10.1.1.8] (wormhole.solnet [10.1.1.254]) (authenticated
 bits=0)
	by rhea.solnet (8.13.6/8.13.6) with ESMTP id k4R9bUcR008228
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO)
	for <pgsql-sql@postgresql.org>; Sat, 27 May 2006 11:37:33 +0200
Message-ID: <44781BCC.7000607@solnet.homeip.net>
Date: Sat, 27 May 2006 11:28:44 +0200
From: "Jesper K. Pedersen" <jkp@solnet.homeip.net>
User-Agent: Mozilla Thunderbird 1.0.7-1.1.fc4 (X11/20050929)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: PLEASE  help ME , HOW TO GENERATE PRIMARY Keys on the fly
References: <20060526101207.5C4B35B0B83@svr4.postgresql.org>
	<20060526105037.GB5492@phlogiston.dyndns.org>
In-Reply-To: <20060526105037.GB5492@phlogiston.dyndns.org>
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200605/195
X-Sequence-Number: 25226

<!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>