5 different ways to find TOP N queries
Let us write five different queries to list the top 4 highest paid employees from emp table.
1) Using ROWNUM
2) Using ROW_NUMBER()
3) Using RANK()
4) Using DENSE_RANK()
5) Using MINUS
1) Using ROWNUM
select * from (select empno,salary,rownum as rk from emp order by salary desc) where rk < 5;
2) Using ROW_NUMBER()
select * from (select empno,salary,row_number() over(order by salary desc) as rk from emp) where rk < 5;
3) Using RANK()
select * from (select empno,salary,rank() over(order by salary desc) as rk from emp) where rk < 5;
4) Using DENSE_RANK()
select * from (select empno,salary,dense_rank() over(order by salary desc) as rk from emp) where rk < 5;
Note that dense_rank will return multiple records if there is a tie.
5) Using MINUS
select empno,salary from emp
minus
select empno,salary from (select empno,salary,rownum as rk from emp order by salary desc) where rk > 4;
You can also read – Nth highest salary
