Re: returning row numbers in select

Maybe by using a sequence and a function.

The sequece to generate the row number.

The a function
1) to reset the sequence and
2) to perform a select with the first column nextval(seq) and the column the
timestamp

However, I am a newbie with PostgreSQL and I am not sure it this will work
correctly...you might have to play with it (or wait for somebody with more
experience than me).

Bernard

On Wednesday 10 March 2004 16:23, Randall Skelton wrote:
> >> I to return a sequential row number beginning at 1?
> >>
> >> i.e.
> >>
> >> row|         timestamp
> >> ---+----------------------------
> >> 1   2004-02-01 23:15:00.824-05
> >> 2   2004-02-01 23:15:01.824-05
> >> 3   2004-02-01 23:15:02.824-05
> >> ...
> >>
> >> My reason for wanting row numbers is so I can use a 'MOD(row_number,
> >> n)' to get the nth row from the table.
>
> Correction, I don't want to simply get the nth row, I want all rows
> that are divisible by n.  Essentially, the timestamp is at a regular
> interval and I want a way of selecting rows at different sampling
> intervals.
>
> > Doesn't LIMIT and OFFSET do the job?
> > http://www.postgresql.org/docs/7.3/interactive/queries-limit.html
>
> It would if I only wanted an offset butI want a query to return the
> first, fifth, and tenth, and so on row.  This would be 'MOD(row_num,
> 5)' but given that I don't know the number of rows a priori, it is
> difficult to write a LIMIT.  Moreover, the offset doesn't make it easy
> to get the first row.  Unless, of course, I am missing something
> obvious?
>
> Cheers,
> Randall
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)