Top-N Queries

Top-N queries provide a method for limiting the number of rows returned from ordered sets of data. The following example shows a common trap people fall into when they don't understand the way the ROWNUM pseudocolumn and ORDER BY clause interact.

First we create a test tables, with the data loaded in reverse order.

CREATE TABLE rownum_order_test (
  id  NUMBER
);

INSERT INTO rownum_order_test (id) VALUES (10);
INSERT INTO rownum_order_test (id) VALUES (9);
INSERT INTO rownum_order_test (id) VALUES (8);
INSERT INTO rownum_order_test (id) VALUES (7);
INSERT INTO rownum_order_test (id) VALUES (6);
INSERT INTO rownum_order_test (id) VALUES (5);
INSERT INTO rownum_order_test (id) VALUES (4);
INSERT INTO rownum_order_test (id) VALUES (3);
INSERT INTO rownum_order_test (id) VALUES (2);
INSERT INTO rownum_order_test (id) VALUES (1);
COMMIT;

We want the smallest 5 records, so we limit the rows returned using the ROWNUM pseudocolumn and order the data by the ID column.

SELECT id
FROM   rownum_order_test
WHERE  rownum <= 5
ORDER BY id;

        ID
----------
         6
         7
         8
         9
        10

5 rows selected.

SQL>

It doesn't do what we wanted it to!

The problem is that the ROWNUM check is performed prior to the ORDER BY operation, resulting in potentially random data being returned. To solve this problem we must use a "Top-N" style query. This utilizes an ordered inline view to force the data into the correct order, then uses the ROWNUM check to limit the data returned.

SELECT id
FROM   (SELECT id
        FROM   rownum_order_test
        ORDER BY id)
WHERE rownum <= 5;

        ID
----------
         1
         2
         3
         4
         5

5 rows selected.

SQL>

So now the ROWNUM check is performed after the ORDER BY operation and we get the result we wanted.

Conversely, we can return the 5 biggest values by altering the ORDER BY clause.

SELECT id
FROM   (SELECT id
        FROM   rownum_order_test
        ORDER BY id DESC)
WHERE rownum <= 5;

        ID
----------
        10
         9
         8
         7
         6

5 rows selected.

SQL>

Hope this helps. Regards Tim...

Back to the Top.