Re: How to select and result row number??

Yudie wrote:
> Hi,
>
> I would like to select and also give numbering in each record.
> Let say I have table 'City' with a 'cityname' column
>
> what is the select statement so I can get result records like this
>
> no cityname
> ---- --------------
> 1 NEW YORK
> 2 LOS ANGELES
> 3 HOUSTON
> 4 ....
> 5 ...

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;
SELECT * FROM out;

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