Oracle SQL query top-n rows

Oracle SQL query top-n rows

Question: I have made a report, and it counts the occurrences of a column, sorts it and displays the top 20 rows. Now I only want to show the top 5 records how do I do this? What is the best way to perform top-n SQL queries?

Answer: If you are using Oracle 9i and beyond you can use the RANK function for top-n queries, with a faster execution plan (WINDOW SORT PUSHED RANK).

select
*
from
(select empno, sal, rank()
over (order by sal desc ) rnk
from emp)
where rnk <= 5;


Oracle also has the row_number function to speed-up top-n queries:

select

*
from
(select empno, sal row_number() over (order by sal desc) rnk from emp)
where rnk <= 5;

Here is the SQL to display the top-n items using ROWNUM. But beware, this technique of using ROWNUM to materialize an in-line view is not efficient, and it's better to materialize the subquery using the SQL-99 WITH clause.

SELECT
ENAME,
SAL
FROM
(SELECT ENAME, SAL
FROM EMP
ORDER BY SAL DESC)
WHERE
ROWNUM < 4;

Beware! This use of ROWNUM< can cause performance problems. Using ROWNUM may change the all_rows optimizer mode for a query to first_rows, causing unexpected sub-optimal execution plans. One solution is to always include an all_rows hint when using ROWNUM to perform a top-n query