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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073