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
ROWNUM < 5returns 4 rows; useROWNUM <= 5to get 5 rows.- Options 1, 2 and 4 keep ties; option 3 cuts strictly at the requested number of rows.