Hi,
I am trying to return specific rows in a query using rownum. I can do
this using between or equality clause but not with a where in clause
for example here are 4 examples the first two fail and the last two
succeed as I would expect. Is this a bug in H2?
Thanks
David
select *
from (
select ROWNUM() as rank, *
from ( select *
from test
order by timing desc)
)
where rank in (1,2,3);
RANK CAR ROUTE TIMING
1 Mazda6 Main Roads 244
2 Mazda6 Main Roads 239
3 Mazda6 Main Roads 234
1 Mazda6 Main Roads 244
2 Mazda6 Main Roads 239
3 Mazda6 Main Roads 234
1 Mazda6 Main Roads 244
2 Mazda6 Main Roads 239
3 Mazda6 Main Roads 234
(9 rows, 6 ms)
select *
from (
select ROWNUM() as rank, *
from ( select *
from test
order by timing desc)
)
where rank = 1 or rank = 2 or rank = 3;
RANK CAR ROUTE TIMING
1 Mazda6 Main Roads 244
2 Mazda6 Main Roads 239
3 Mazda6 Main Roads 234
1 Mazda6 Main Roads 244
2 Mazda6 Main Roads 239
3 Mazda6 Main Roads 234
1 Mazda6 Main Roads 244
2 Mazda6 Main Roads 239
3 Mazda6 Main Roads 234
(9 rows, 3 ms)
select *
from (
select ROWNUM() as rank, *
from ( select *
from test
order by timing desc)
)
where rank >= 1 and rank <= 3;
RANK CAR ROUTE TIMING
1 Mazda6 Main Roads 244
2 Mazda6 Main Roads 239
3 Mazda6 Main Roads 234
(3 rows, 3 ms)
select *
from (
select ROWNUM() as rank, *
from ( select *
from test
order by timing desc)
)
where rank between 1 and 3;
RANK CAR ROUTE TIMING
1 Mazda6 Main Roads 244
2 Mazda6 Main Roads 239
3 Mazda6 Main Roads 234
(3 rows, 4 ms)
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
