Dev Corner

We often need to limit the number of rows returned by a select statement. Some database servers provide extensions to the select statement. For example, MySql database sever provides LIMIT directive. In Oracle Database each result set has defined a ROWNUM pseudocolumn and ORDER BY clause interact.

Let’s create a test table with some data loaded in reverse order.

CREATE TABLE numbers (
  id  NUMBER
);
 
INSERT INTO numbers (id) VALUES (10);
INSERT INTO numbers (id) VALUES (9);
INSERT INTO numbers (id) VALUES (8);
INSERT INTO numbers (id) VALUES (7);
INSERT INTO numbers (id) VALUES (6);
INSERT INTO numbers (id) VALUES (5);
INSERT INTO numbers (id) VALUES (4);
INSERT INTO numbers (id) VALUES (3);
INSERT INTO numbers (id) VALUES (2);
INSERT INTO numbers (id) VALUES (1);
COMMIT;

We want the first 3 rows from the SELECT result set:

SELECT * FROM numbers WHERE ROWNUM <= 3;
 
     ID
-------
     10
      9
      8

We want to retrieve only the records with the smallest 3 IDs.

SELECT * FROM numbers 
 WHERE ROWNUM <= 3
 ORDER BY id;
 
     ID
-------
      8
      9
     10

The result is not what we expected. The reason is that Oracle first fetches the records based on selection predicate and than sort the resulting rows. In our case, Oracle first filters the first three rows with IDs 10, 9 and 8 and than sorts them.

To achieve the desired result we need first to sort the records and then select the first 3 rows. For this we can use nested selects:

SELECT * FROM (SELECT * FROM numbers
                ORDER BY id)
 WHERE ROWNUM <= 3;
 
     ID
-------
      1
      2
      3

The Oracle first executes the innermost sub-select. Thus Oracle fetches all the rows and orders them. Than Oracle Database executes the outer select which filters only the first 3 rows.

We can use the same technique to retrieve the top 2 rows, i.e. the 2 rows with the biggest ID:

SELECT * FROM (SELECT * FROM numbers
                ORDER BY id DESC)
 WHERE ROWNUM <= 2;
 
     ID
-------
     10
      9

Select queries that return the first N rows from an ordered result set are also known as Top-N queries.

References

1. Top-N Queries [http://www.oracle-base.com/articles/misc/TopNQueries.php]
2. Top N Oracle Database SELECT Rows