Top-N queries provide a method for limiting the number of rows returned from sets (mostly ordered) of data.

NOTE: Oracle does not guarantee that it will return the result of an SQL statement in the order the data was inserted into the table(s). Only way of achieving that is to explicitly uses the ORDER BY clause. Let’s see.

Let’s prepare the table first.

Create table test(a number);

INSERT INTO test (a) VALUES (6);
INSERT INTO test (a) VALUES (5);
INSERT INTO test (a) VALUES (4);
INSERT INTO test (a) VALUES (3);
INSERT INTO test (a) VALUES (2);
INSERT INTO test (a) VALUES (1);
COMMIT;

We want the smallest 3 records, so we limit the rows returned using the rownum and order the data by the column (A number).

SQL> select * from test
  2  where rownum<=3
  3  order by a;

NOT what we wanted, right?

What happens is that the rownum <=3 check is performed prior to the order by clause.

So let's use a "Top-N" style query which utilizes an ordered inline view to force the data into the correct order, then performs the rownum<=3 check to limit the data returned.

SQL> select a from (select a from test order by a)
  2  where rownum<=3;

Now if we wanted 5 biggest values out of the table, all we have to do is change the order of data in the inline view.

SQL> select a from (select a from test order by a desc)
  2  where rownum<=3;

Hope this helps.

Kirtan Desai