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

TOP-N queries in Oracle

create table top_n_test (
   a number,
   b varchar2(10)
);
insert into top_n_test values (1,   'one');
insert into top_n_test values (2,   'two');
insert into top_n_test values (3, 'three');
insert into top_n_test values (4,  'four');
insert into top_n_test values (5,  'five');
insert into top_n_test values (6,   'six');
insert into top_n_test values (7, 'seven');
insert into top_n_test values (8, 'eight');
insert into top_n_test values (9,  'nine');

commit;
Find the first three (alphabetically-wise) numbers:
select a, b from (
  select
    a,b,
    rank() over (order by b) r
  from
    top_n_test
  )
where
  r<4;
         A B
---------- ----------
         8 eight
         5 five
         4 four

Related links