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