Re: [GENERAL] using a sequence as the functional equivalent to Oracle

[Oisin Glynn]

Wm.A.Stafford wrote:

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.

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


I get the following:

rownum   id
-------+---------
 11        10038
 12        10809
 13        10810
 14        22549
 15        23023





Thanks,
-=bill



---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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 " However, if I add a where clause referencing rownum for example: where id>0 and rownum>0 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?

I have done this using limit and offset like the following


Oisin

select * from foo order by bar limit 10 offset 50;--giving the 10 rows from position 51 onwards (offset is zero based)

smime.p7s
Description: S/MIME Cryptographic Signature