Friday, August 13, 2010

How does one select the TOP N rows from a table?

After Oracle 9i there is the RANK() and DENSE_RANK() functions which can be used to determine TOP N rows.

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

Way 1: Using RANK()

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

Way 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;

Way3: 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;

Way 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;

No comments:

Post a Comment