paging with select statements and rownum

[nottingham]

Hi. I'm trying to select a page of 10 items, starting at the 12th
item, out of a table of 100. Googling around a bit, this is the
recommended way to do this:

select * from (select rownum as rnum, row.* from (select * from test
order by id) row where rownum <= 21) where rnum >= 12;

This gives me back nothing. I'm fairly certain this works in oracle
as is. So is this a bug in h2 or am i just doing something wrong?
Thanks.

[Brian]

H2 doesn't have a native rownum like Oracle does. That said, you may
be able to approximate it with the ROWNUM() function.

SELECT ROWNUM(), * FROM (SELECT * FROM TEST ORDER BY NAME) where
ROWNUM() < 22 and ROWNUM() > 11;

[nottingham]

That doesn't work either. I tried this in the web console:

drop table test;
create table test(id int primary key);
insert into test values(1);
insert into test values(2);
insert into test values(3);
insert into test values(4);
insert into test values(5);
select rownum(), * from test;
select rownum(), * from test order by id desc;
select rownum(), * from(select * from test order by id desc);
select rownum(), * from(select * from test order by id desc) where
rownum() < 3 and rownum() > 1;

Last select returns nothing. I also tried this, which is just the
standard paging sql using rownum():

select * from (select rownum() as rnum, row.* from (select * from test
order by id desc) row where rownum() < 4) where rnum > 2;

still nothing. What's interesting is that this sql will give
something back:

select * from (select rownum() as rnum, row.* from (select * from test
order by id desc) row where rownum() < 4) where rnum > 1;

It seems to only work if it's selecting the first page.

[Nitai @ Razuna]

Not true. If you run H2 in Oracle mode, you wil have the "rownum".

> --
> You received this message because you are subscribed to the Google Groups "H2 Database" group.
> To post to this group, send email to h2-da...@googlegroups.com.
> To unsubscribe from this group, send email to h2-database...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
>
>

--
See for yourself how easy it is to manage files today. Join the revolution!

Razuna SaaS On-Demand - Hosted Digital Asset Management Solution
http://www.razuna.com/

Razuna - Open Source Digital Asset Management
http://www.razuna.org/

Follow us on Twitter
http://twitter.com/razunahq

[Rami Ojares]

And why fool around with this superfluous concept of row numbers (which
do not belong to the relational model that is set based)

If you want to "page" the returned results why not make a query like:

select * from test order by id desc limit 10 offset 11.

- rami

[Steve McLeod]

Rami's solution is the correct way to do this in H2.

select * from test order by id desc limit 10 offset 11

The rownum() function in Oracle is an Oracle-specific non-standard SQL
enhancement.

Regards,

Steve

[nottingham]

Hmm...that's something of a problem since i'm using H2 specifically as
an embedded database for unit tests so i don't have to touch oracle.
I used to use Hypersonic, but i moved to H2 because it operates much
closer to Oracle, specifically in it's SQL syntax which is almost
identical.

So, even if the limit/offset method works, shouldn't the rownum method
work when in Oracle mode? The rownum method will work in H2 if you
replace the final 'where' with a 'group by rnum having rnum > 2'.

[Thomas Mueller]

Hi,

There is a bug in H2, currently you can't use ROWNUM for what you
want. I will fix that. Test case:

drop table test;
create table test(id int);

insert into test values(1);
insert into test values(2);
insert into test values(3);
insert into test values(4);

select * from test where rownum > 2;
select * from test where rownum > 2 order by id;
select * from test where rownum < 2 order by id desc;
select * from test where rownum < 2;

What you probably want (but this doesn't work with H2 currently):

select id from (select t.*, rownum as r from test t) where r between 2 and 3;

See also: http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html

With the current version of H2, you need to use LIMIT / OFFSET.

Regards,
Thomas