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