|
Sometimes, someone is tempted to select the first n rows of a table. In order to demonstrate that, the following table is created and populated:
set feedback off
create table items (
id number primary key,
name varchar2(20),
price number(7,2)
);
insert into items values ( 1, 'cup', 1.20);
insert into items values ( 2, 'book', 49.99);
insert into items values ( 3, 'mobile', 89.99);
insert into items values ( 4, 'coke', 0.78);
insert into items values ( 5, 'pencil', 1.35);
insert into items values ( 6, 'dollar', 1.00);
insert into items values ( 7, 'door', 150.00);
insert into items values ( 8, 'oracle', 19999.00);
insert into items values ( 9, 'carpet', 122.40);
insert into items values (10, 'apple', 1.05);
insert into items values (11, 'table', 198.00);
insert into items values (12, 'cd/r', 1.20);
insert into items values (13, 'back pack', 21.53);
insert into items values (14, 'laptop', 999.50);
insert into items values (15, 'air', 0.00);
insert into items values (16, 'tv', 310.00);
insert into items values (17, 'color', 2.22);
insert into items values (18, 'bun', 2.50);
insert into items values (19, 'egg', 0.80);
insert into items values (20, 'bike', 1250.00);
Usually, this can be solved with rownum. The following example retrieves the first 5 rows:
NAME PRICE
-------------------- ----------
cup 1.2
book 49.99
mobile 89.99
coke .78
pencil 1.35
This was easy. But this is also where the problems start.
One problem is: how to select the rows 6 though 10? The following (naive) approach does not work:
select name, price
from items
where rownum > 5 and
rownum < 11;
This is because the pseudo column rownum never reaches 6. Rownum counts actually returned rows. In order for where rownum > 5 to be true, 5 rows must already have returned, but they are not, because these were excluded through exactly this where clause. This dilemma can be solved with a nested select:
select name, price
from (
select rownum r, name, price
from items
)
where r > 5 and
r < 11;
NAME PRICE
-------------------- ----------
dollar 1
door 150
oracle 19999
carpet 122.4
apple 1.05
This works because Oracle first evaluates the inner select statement and returns all records with an increasing rownum. The outer where clause can then select the rows it needs.
However, there are more problems. The most important one seems to be: what exaclty does first mean.
For example, say, we want to retrieve the five cheapest items.
select name, price
from items
where rownum < 6
order by price;
NAME PRICE
-------------------- ----------
coke .78
cup 1.2
pencil 1.35
book 49.99
mobile 89.99
This is clearly wrong. For example, the egg is missing which only costs 0.80 (currency units). What happens? Oracle first retrieves the first five rows and then orders them by price. This is a consequence of the fact that we didn't explicitely enough state what we meant with first.
select name, price
from (
select name, price, row_number() over (order by price) r
from items
)
where r between 1 and 5;
NAME PRICE
-------------------- ----------
air 0
coke .78
egg .8
dollar 1
apple 1.05
If the 6th to the 10th row must be returned, the where clause should read where r between 6 and 10
|