From: | "Wm(dot)A(dot)Stafford" <stafford(at)marine(dot)rutgers(dot)edu> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | using a sequence as the functional equivalent to Oracle rownum |
Date: | 2006-11-28 20:19:43 |
Message-ID: | 456C99DF.8000002@marine.rutgers.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I'm trying to use a temporary sequence to duplicate the functionality of
the Oracle rownum pseudo-column
as suggested by Scott Marlow in the archives:
http://archives.postgresql.org/pgsql-sql/2005-05/msg00126.php.
The Oracle based application I'm porting to PostgreSQL used rownum to
select the 'next' block of rows to
process by specifying a where clause with something like " where
rownum>x and rownum<y "
My basic PostgreSQL query is:
drop sequence rownum ;
create temp sequence rownum;
select B.rownum , B.id from
(select nextval('rownum') as rownum, A.* from
(select distinct id from ... where ... order by ... DESC
) as A
) as B
where id>0
This basic query produces the following result set:
rownum id
--------+---------
1 10038
2 10809
3 10810
4 22549
5 23023
However, if I add a where clause referencing rownum for example: where
id>0 and rownum>0
I get the following:
rownum id
-------+---------
11 10038
12 10809
13 10810
14 22549
15 23023
It appears as if rownum has been incremented as a result of three passes
over the five row result set.
Can someone explain what is going on? And more to to point, if this is
expected behavior, is there a standard PostgreSQL way to select a
'block' of rows from a result set based on row number?
Thanks,
-=bill
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2006-11-28 20:23:36 | Re: backend crash following load command |
Previous Message | Martijn van Oosterhout | 2006-11-28 20:14:22 | Re: backend crash following load command |