returning row numbers in select

From: Randall Skelton <skelton(at)brutus(dot)uwaterloo(dot)ca>
To: pgsql-general(at)postgresql(dot)org
Subject: returning row numbers in select
Date: 2004-03-10 20:25:04
Message-ID: 03A9CA5B-72D1-11D8-BD91-000393C92230@brutus.uwaterloo.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

For a rather simple query:

SELECT timestamp from test
WHERE timestamp > '2004-02-02 04:15:00.00 +0'
AND timestamp < '2004-02-02 04:15:10.00 +0';

where 'test' is

Column | Type | Modifiers
-----------+--------------------------+-----------
timestamp | timestamp with time zone |
value | double precision |
Indexes: table_timestamp

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.

Cheers,
Randall

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bruno Wolff III 2004-03-10 20:43:42 symbolic links for 7.4.2
Previous Message Steve Wolfe 2004-03-10 20:14:38 Re: Question on Opteron performance