Search Postgresql Archives

Re: using a sequence as the functional equivalent to Oracle

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.

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



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

I have done this using limit and offset like the following
select * from foo order by bar limit 10 offset 50;--giving the 10 rows from position 51 onwards (offset is zero based)

Oisin


Attachment: smime.p7s
Description: S/MIME Cryptographic Signature


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux