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