René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

Selecting the first n rows with Oracle

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:
select name, price 
  from items
where rownum < 6;
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;
no rows selected
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.
This problem can be solved with row_number (which happens to be a analytical function).
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.

Related links