Re: returning row numbers in select

On Wednesday 10 March 2004 12:25 pm, Randall Skelton wrote:
> Is there a way to return an integer row number for a query?  Note
> that there may be a large number of rows so I would rather not have
> joined selects...
<snip>

Well...if your result has a unique column you can do something like
this:

steve=# select (select count(*) from bar as barcount where
barcount.sec<=bar.sec) as rownum, sec from bar order by sec;

 rownum |    sec
--------+------------
      1 | 1063966688
      2 | 1063966689
      3 | 1063966690
      4 | 1063966691
      5 | 1063966692
      6 | 1063966693
      7 | 1063966694
      8 | 1063966695
      9 | 1063966696
     10 | 1063966697
     11 | 1063966698
     12 | 1063966699
     13 | 1063966700
     14 | 1063966701
     15 | 1063966702
     16 | 1063966703
     17 | 1063966704
     18 | 1063966705

As you might guess, this is not a fast query - more of a brute-force
kludge. It's likely that you will be better off postprocessing the
query to select every n records or possibly writing a function that
will handle the situation.

Cheers,
Steve