8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Misc » Here

Top-N Queries

Top-N queries provide a method for limiting the number of rows returned from ordered sets of data. They are extremely useful when you want to return the top or bottom "N" number of rows from a set or when you are paging through data. This article presents several methods to implement Top-N queries.

Related articles.

Setup

First we must create and populate a test table.

DROP TABLE rownum_order_test;

CREATE TABLE rownum_order_test (
  val  NUMBER
);

INSERT ALL
  INTO rownum_order_test
  INTO rownum_order_test
SELECT level
FROM   dual
CONNECT BY level <= 10;

COMMIT;

The following query shows we have 20 rows with 10 distinct values.

SELECT val
FROM   rownum_order_test
ORDER BY val;

       VAL
----------
         1
         1
         2
         2
         3
         3
         4
         4
         5
         5
         6

       VAL
----------
         6
         7
         7
         8
         8
         9
         9
        10
        10

20 rows selected.

SQL>

What not to do!

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. Let's assume we wanted to return the top 5 values in the ID column. We might decide to order the data by descending ID and pick off the first five rows. That sounds correct, so we go ahead and issue the following query.

SELECT val
FROM   rownum_order_test
WHERE  rownum <= 5
ORDER BY val DESC;

       VAL
----------
         5
         4
         3
         2
         1

5 rows selected.

SQL>

That didn't do what we wanted!

The problem is that the ROWNUM assignment is performed prior to the ORDER BY operation, resulting in potentially random data being returned.

Row Limiting Clause (12c onward)

If you are using Oracle 12c onward, Top-N queries just got a whole lot easier with the introduction of the Row Limiting Clause. If backwards compatibility is not your concern, switch to using this new clause. There is a quick example below, but the separate article explains all the ways of using this new syntax.

SELECT val
FROM   rownum_order_test
ORDER BY val DESC
FETCH FIRST 5 ROWS ONLY;

       VAL
----------
        10
        10
         9
         9
         8

5 rows selected.

SQL>

Inline View and ROWNUM

The classic Top-N style query uses an ordered inline view to force the data into the correct order, then uses the ROWNUM check to limit the data returned.

SELECT val
FROM   (SELECT val
        FROM   rownum_order_test
        ORDER BY val DESC)
WHERE ROWNUM <= 5;

       VAL
----------
        10
        10
         9
         9
         8

5 rows selected.

SQL>

As the data is in the desired order before the ROWNUM check is performed, we get the result we wanted. Notice that we asked for 5 rows and we got five, even though there is a second row with the value "8".

We can return the 5 smallest values by altering the ORDER BY clause to ascending.

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

       VAL
----------
         1
         1
         2
         2
         3

5 rows selected.

SQL>

This method can also be used for paging through data, like paged web reports.

SELECT val
FROM   (SELECT val, rownum AS rnum
        FROM   (SELECT val
                FROM   rownum_order_test
                ORDER BY val)
        WHERE rownum <= 8)
WHERE  rnum >= 4;

       VAL
----------
         2
         3
         3
         4
         4

5 rows selected.

SQL>

This looks like it might not perform well, but we can see from the execution plan that Oracle can push the predicates down into the inline views to make them much more efficient, so this is the best way to page through data using SQL.

Execution Plan
----------------------------------------------------------
Plan hash value: 2927523340

----------------------------------------------------------------------------------------------
| Id  | Operation                | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                   |     8 |   208 |     4  (25)| 00:00:01 |
|*  1 |  VIEW                    |                   |     8 |   208 |     4  (25)| 00:00:01 |
|*  2 |   COUNT STOPKEY          |                   |       |       |            |          |
|   3 |    VIEW                  |                   |   100 |  1300 |     4  (25)| 00:00:01 |
|*  4 |     SORT ORDER BY STOPKEY|                   |   100 |  1300 |     4  (25)| 00:00:01 |
|   5 |      TABLE ACCESS FULL   | ROWNUM_ORDER_TEST |   100 |  1300 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RNUM">=4)
   2 - filter(ROWNUM<=8)
   4 - filter(ROWNUM<=8)

WITH Clause and ROWNUM

The previous example can be rewritten to use a WITH clause in place of the inline view.

WITH ordered_query AS
  (SELECT val
   FROM   rownum_order_test
   ORDER BY val DESC)
SELECT val
FROM   ordered_query
WHERE  rownum <= 5;

       VAL
----------
        10
        10
         9
         9
         8

5 rows selected.

SQL>

RANK

The RANK analytic function assigns a sequential rank for each distinct value in the specified window.

SELECT val
FROM   (SELECT val,
               RANK() OVER (ORDER BY val DESC) AS val_rank 
        FROM   rownum_order_test)
WHERE  val_rank <= 5;

       VAL
----------
        10
        10
         9
         9
         8
         8

6 rows selected.

SQL>

At first glance this looks like there may be a problem, but displaying the rank information shows us what is happening.

SELECT val, val_rank
FROM   (SELECT val,
               RANK() OVER (ORDER BY val DESC) AS val_rank 
        FROM   rownum_order_test)
WHERE  val_rank <= 5;

       VAL   VAL_RANK
---------- ----------
        10          1
        10          1
         9          3
         9          3
         8          5
         8          5

6 rows selected.

SQL>

From this we can see that duplicate rows are assigned the same rank, followed by a skip in the sequence to keep the rank consistent. Similar to Olympic medal places. This means the RANK function doesn't give us the "top N rows" or the "top N distinct values". The number of rows returned is dependent on the number of duplicates in the data.

There is an article on the RANK analytic function here.

DENSE_RANK

The DENSE_RANK analytic function is similar to the RANK analytic function in that it assigns a sequential rank for each distinct value in the specified window. The difference being the ranks are compacted, so there are no gaps.

SELECT val
FROM   (SELECT val,
               DENSE_RANK() OVER (ORDER BY val DESC) AS val_rank 
        FROM   rownum_order_test)
WHERE  val_rank <= 5;

       VAL
----------
        10
        10
         9
         9
         8
         8
         7
         7
         6
         6

10 rows selected.

SQL>

Displaying the rank information shows us what is happening.

SELECT val, val_rank
FROM   (SELECT val,
               DENSE_RANK() OVER (ORDER BY val DESC) AS val_rank 
        FROM   rownum_order_test)
WHERE  val_rank <= 5;

       VAL   VAL_RANK
---------- ----------
        10          1
        10          1
         9          2
         9          2
         8          3
         8          3
         7          4
         7          4
         6          5
         6          5

10 rows selected.

SQL>

Once again, duplicate values are assigned the same rank, but there is no gap in the rank sequence. As a result DENSE_RANK always gives us a "top N distinct values" result.

There is an article on the DENSE_RANK analytic function here.

ROW_NUMBER

The ROW_NUMBER analytic function is similar to the ROWNUM pseudocolumn in that it assigns a unique number for each row returned, but like all analytic functions its action can be limited to a specific window of data in the result set and based on the order of data in that window. In this simple example using a window of the whole result set it functions the same as the ROWNUM psuedocolumn.

SELECT val
FROM   (SELECT val,
               ROW_NUMBER() OVER (ORDER BY val DESC) AS val_row_number 
        FROM   rownum_order_test)
WHERE  val_row_number <= 5;

       VAL
----------
        10
        10
         9
         9
         8

5 rows selected.

SQL>

There is an article on the ROW_NUMBER analytic function here.

PERCENT_RANK

The PERCENT_RANK analytic function assigns value between 0-1 which represents the position of the current row relative to the set as a percentage. The following example displays the top 80% of the rows based on the value.

SELECT val
FROM   (SELECT val,
               PERCENT_RANK() OVER (ORDER BY val) AS val_percent_rank 
        FROM   rownum_order_test)
WHERE  val_percent_rank >= 0.8;

       VAL
----------
         9
         9
        10
        10

4 rows selected.

SQL>

There is an article on the PERCENT_RANK analytic function here.

NTILE

The NTILE analytic function divides the rows into the specified number of buckets, each with approximately equal numbers of rows. The following example divides the results into three buckets, and shows the rows from the third bucket, or the top third of the results.

SELECT val
FROM   (SELECT val,
               NTILE(3) OVER (ORDER BY val) AS val_ntile 
        FROM   rownum_order_test)
WHERE  val_ntile = 3;

       VAL
----------
         8
         8
         9
         9
        10
        10

6 rows selected.

SQL>

There is an article on the NTILE analytic function here.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.