Skip site navigation (1) Skip section navigation (2)

using a sequence as the functional equivalent to Oracle rownum

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: (view raw, whole thread or download thread mbox)
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:

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 , 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?



pgsql-general by date

Next:From: Tom LaneDate: 2006-11-28 20:23:36
Subject: Re: backend crash following load command
Previous:From: Martijn van OosterhoutDate: 2006-11-28 20:14:22
Subject: Re: backend crash following load command

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group