using a sequence as the functional equivalent to Oracle rownum
От | Wm.A.Stafford |
---|---|
Тема | using a sequence as the functional equivalent to Oracle rownum |
Дата | |
Msg-id | 456C99DF.8000002@marine.rutgers.edu обсуждение исходный текст |
Ответы |
Re: using a sequence as the functional equivalent to Oracle rownum
Re: using a sequence as the functional equivalent to Oracle |
Список | 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
В списке pgsql-general по дате отправления: