RANK Function

RANK Function:

Each entry can be ranked based on any of the given criteria. For e.g:- grading system in a school.

Syntax:
Function
(arg1,arg2)
OVER(
Partition Clause-Sort as per a particular criteria(eg.job,grade)
Sorting Clause-Normal sort of full record
Windowing Clause- sort based on any one record value.
)


Function -Rank, Dense Rank, Row number

Rank:

1 2 3 3 - Two person are in tie while ranking both will be given same ranking by the next rank will be  added value of one more position i.e

1 2 3 3 5- the 3rd position has a tie and occupies 2 position with same ranking, so the next person will be pushed to 5th in order ranking.

E.g:

Select empno,empname,job,sal,
rank() OVER (order by sal) as rank_sal from emp order by sal.

Dense Ranking:

1 2 3 3 4 -  the 3rd position has a tie and occupies 2 position with same ranking, but the next person will be ranked to next sequence 4th.


Select empno,empname,job,sal,
dense_rank() OVER (order by sal) as rank_sal from emp order by sal.



Row number

1 2 3 4 5 6  -Tie records will be ordered as per the column we add to order and the tie will replaced to sequential

Based on what field we need to position the tie records we can mention in sorting clause.

E.g:

Select empno,empname,job,sal,
row_number() OVER (order by sal,empno) as rank_sal from emp order by sal.

here empno column next to order by clause indicates that the tie should be ranked with precedance of employee number.