Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 3647F475CA6
	for <pgsql-sql@postgresql.org>; Tue, 17 Sep 2002 17:56:47 -0400 (EDT)
Received: from candle.pha.pa.us (momjian.navpoint.com [207.106.42.251])
	by postgresql.org (Postfix) with ESMTP id 2BE71475AD7
	for <pgsql-sql@postgresql.org>; Tue, 17 Sep 2002 17:56:44 -0400 (EDT)
Received: (from pgman@localhost)
	by candle.pha.pa.us (8.11.6/8.10.1) id g8HLisd23301;
	Tue, 17 Sep 2002 17:44:54 -0400 (EDT)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200209172144.g8HLisd23301@candle.pha.pa.us>
Subject: Re: How to select and result row number??
In-Reply-To: <20020917144635.GB6538@rice.edu>
To: "Ross J. Reedstrom" <reedstrm@rice.edu>
Date: Tue, 17 Sep 2002 17:44:53 -0400 (EDT)
Cc: Roland Roberts <roland@astrofoto.org>,
	Yudie <yudie@axiontech.com>, pgsql-sql@postgresql.org
X-Mailer: ELM [version 2.4ME+ PL99 (25)]
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset=US-ASCII
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200209/197
X-Sequence-Number: 9474

Ross J. Reedstrom wrote:
> On Mon, Sep 16, 2002 at 11:12:06PM -0400, Bruce Momjian wrote:
> > Yudie wrote:
> <wants numbered records>
> 
> > Good question.  The only easy answer I have is the creation of a temp
> > table with a SERIAL column:
> > 
> > 	CREATE TEMP TABLE out (cnt SERIAL, other_cols...);
> > 	INSERT INTO out SELECT ... ORDER BY col;
> 
> Hmm, this needs to be:
> 
>  	INSERT INTO out (ther_cols...) SELECT ... ORDER BY col;
> 
> So that the cnt column gets filled from the default.

Yes, thanks for the fix.

> >     create sequence temp_counter;
> >     select nextval('temp_counter'), * from whatever;
> > 
> > If you have an 'order by', the above will not work.  You could then
> > try either building a temporary table or using a subselect
> > 
> >     select nextval('temp_counter'), * from (select .... order by ...);
> 
> Approximately the same solution, but without saving the result in a temp
> table.

I thought about doing it this way.  However, a subselect as a
pseudotable is not guaranteed to return the data in any specific order,
so I don't think this method work work reliably.  At least that was my
assumption.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073