LLI Wiki — My Digital Knowledge Base

Select the top N rows from an Oracle table

Four methods: RANK, DENSE_RANK, inner query with ROWNUM and correlated COUNT DISTINCT.

Four ways to get the top N rows of an Oracle table, here the 5 highest salaries of an employee table.

Option 1: RANK()

Ties get the same rank and the next rank is skipped.

SELECT employee_name, salary
FROM ( SELECT employee_name, salary, RANK() OVER (ORDER BY salary DESC) salary_rank
       FROM employee )
WHERE salary_rank <= 5;

Option 2: DENSE_RANK()

Ties get the same rank and no rank is skipped.

SELECT employee_name, salary
FROM ( SELECT employee_name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) salary_dense_rank
       FROM employee )
WHERE salary_dense_rank <= 5;

Option 3: ordered inner query and ROWNUM

SELECT *
FROM (SELECT * FROM employee ORDER BY salary DESC)
WHERE ROWNUM < 5;

Option 4: correlated COUNT(DISTINCT)

SELECT *
FROM employee e
WHERE 5 >= (SELECT COUNT(DISTINCT salary)
            FROM employee b
            WHERE b.salary >= e.salary)
ORDER BY salary DESC;

Notes

Mastodon LLIMastodon SocialFosstodon