8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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.
- Setup
- What not to do!
- Row Limiting Clause (12c onward)
- Inline View and ROWNUM
- WITH Clause and ROWNUM
- RANK
- DENSE_RANK
- ROW_NUMBER
- PERCENT_RANK
- NTILE
Related articles.
- Top-N Queries
- Row Limiting Clause for Top-N Queries in Oracle Database 12c Release 1 (12.1)
- RANK, DENSE_RANK, FIRST and LAST Analytic Functions
- ROW_NUMBER Analytic Function
- PERCENT_RANK Analytic Function
- NTILE Analytic Function
- WITH Clause : Subquery Factoring in Oracle
- Analytic Functions
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:
- Top-N Queries
- Row Limiting Clause for Top-N Queries in Oracle Database 12c Release 1 (12.1)
- RANK, DENSE_RANK, FIRST and LAST Analytic Functions
- ROW_NUMBER Analytic Function
- PERCENT_RANK Analytic Function
- NTILE Analytic Function
- WITH Clause : Subquery Factoring in Oracle
- Analytic Functions
Hope this helps. Regards Tim...