Objective:
Let us write five different queries to list the top 4 highest paid employees from EMP table.
Methods:
1) Using ROWNUM
2) Using ROW_NUMBER ()
3) Using RANK()
4) Using DENSE_RANK()
5) Using MINUS
Using ROWNUM :
Select * from (select empno, ename,sal, rownum as rk from emp order by sal desc)
Where rk<5;
Result:
Using ROW_NUMBER ():
select * from (select empno,ename,sal,row_number() over (order by sal desc)rk from emp order by sal desc)
where rk<5;
Result:
Using RANK():
select * from (select empno,ename,sal,rank() over (order by sal desc)rk from emp order by sal desc)
where rk<5;
Result:
Using DENSE_RANK():
select * from (select empno,ename,sal,dense_rank() over (order by sal desc)rk from emp order by sal desc)
where rk<5;
Result:
Using MINUS:
select empno,ename,sal from emp
minus
select empno,ename,sal from ( select empno,ename,sal,rownum as rk from emp
order by sal desc)
where rk>4;
Result:
