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

rownum1

1) Using ROWNUM

select * from (select empno,salary,rownum as rk from emp order by salary desc) where rk < 5;

rownum_topN

2) Using ROW_NUMBER()

select * from (select empno,salary,row_number() over(order by salary desc) as rk from emp) where rk < 5;

rownumber_topN

3) Using RANK()

select * from (select empno,salary,rank() over(order by salary desc) as rk from emp) where rk < 5;

rank_topN

4) Using DENSE_RANK()

select * from (select empno,salary,dense_rank() over(order by salary desc) as rk from emp) where rk < 5;

denserank_topN

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;

minus_topN

You can also read – Nth highest salary