5 different ways to find TOP N queries

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 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s