Top-N Queries in Oracle

Top-N Queries are common in Oracle database. People will often look for the below queries

oracle top 100
oracle select top 10
oracle top 1000 rows
selecting top 10 rows in oracle
how to select top 10 rows

These are essentianlly the same queries . They are called Top-N queries

Let’s take a look at the different methods in Oracle to achieve Top-N Queries in Oracle

Pre 12c

Here is the way to obtain the top 5 values

SELECT *
FROM (SELECT *
FROM dept
ORDER BY sales DESC)
WHERE ROWNUM <= 5;

This version will sort Dept by sales descending and then return the first five records it encounters (the top-five records).

Here is the way for selecting top 10 rows in oracle

The below query will give top 10 rows
SELECT *
FROM (SELECT *
FROM dept
ORDER BY sales DESC)
WHERE ROWNUM <= 10;

(2) Using ROW_NUMBER() oracle analytic function: It behaves similarly to the ROWNUM pseudo column but is more flexible and has more capabilities

Here is the way to obtain the top 5 values

SELECT *
FROM (SELECT d.*,row_number() over (ORDER BY d.sales DSC) rn
FROM dept d
)
WHERE rn <= 5;

Similary for 10 rows
SELECT *
FROM (SELECT d.*,row_number() over (ORDER BY d.sales DSC) rn
FROM dept d
)
WHERE rn <= 10;

(3) Using RANK() and DENSE_RANK(): These are analytic functions which can be used to remove the problem stated above
Here is the way to obtain the top 5 values using rank

SELECT *
FROM (SELECT d.*,rank() over (ORDER BY d.sales DSC) rn
FROM dept d
)
WHERE rn <= 5;

Here is the way to obtain the top 5 values using dense_rank

SELECT *
FROM (SELECT d.*,dense_rank() over (ORDER BY d.sales DSC) rn
FROM dept d
)
WHERE rn <= 5;

(4) NTILE and Percent_rank can also be used

With 12c

(5) Top-N feature:

Oracle Database 12c includes support for the ANSI-standard FETCH FIRST/NEXT and OFFSET clauses—together called the row limiting clause. This clause enables you to easily retrieve the first N records from a result set

A Top-N query allows us to retrieve the top or bottom N rows from an ordered set.

Example:

SELECT value
FROM mytable
ORDER BY value DESC
FETCH FIRST 10 ROWS ONLY;  

select * from my_test order by name fetch first 10 rows only;

If you look at the optimizer plan for the above query, it is still using row_number() under the wrap for doing it

Restriction
(1)If you have a SELECT statement with FOR UPDATE, you can’t use it.
(2)The SELECT statement can’t CURRVAL or NEXTVAL of sequences
(3) If the query of the Materialized Views has this clause, then you can’t do an incremental refresh of that Materialized View

Hope you like the article on Top-N Queries in oracle . Please do provide feedback

Also Reads
Lead Function in Oracle
RANK function in Oracle
https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljoffsetfetch.html

Reader Interactions

Leave a Reply Cancel reply