Re: How to select and result row number??

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,
Pennsylvania19073