Date: Fri, 26 May 2006 09:08:20 -0500
From: Bruno Wolff III <bruno@wolff.to>
To: Andrew Sullivan <ajs@crankycanuck.ca>
Cc: pgsql-sql@postgresql.org
Subject: Re: PLEASE  help ME , HOW TO GENERATE PRIMARY Keys on the fly
Message-ID: <20060526140820.GA17450@wolff.to>
Mail-Followup-To: Bruno Wolff III <bruno@wolff.to>,
	Andrew Sullivan <ajs@crankycanuck.ca>, pgsql-sql@postgresql.org
References: <20060526101207.5C4B35B0B83@svr4.postgresql.org>
	<20060526105037.GB5492@phlogiston.dyndns.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20060526105037.GB5492@phlogiston.dyndns.org>
User-Agent: Mutt/1.4.2.1i

On Fri, May 26, 2006 at 06:50:37 -0400,
  Andrew Sullivan <ajs@crankycanuck.ca> wrote:
> On Fri, May 26, 2006 at 05:11:26PM +0700, andi wrote:
> > 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.

The simplest solution is to add the rank information in your application as
it reads the result set.

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

Rollbacks will not reset sequence values. Use setval to do that.