Select the TOP N rows from a table in Oracle

Select the TOP N rows from a table in Oracle

Below is the examples to find the top 5 employees based on their salary.

Option 1: Using RANK()

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: Using Dense_Rank()

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: Using inner query
This is an example of using an inner-query with an ORDER BY clause:

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

Option 4: Using count distinct combination

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