This blog contains Window Ranking function in SQL like (Rank, Dense_Rank, Row_Number , Lead, Lag) .
This
RANK()function calculates a rank to each row within a partition of a result set.
The Syntax of Rank() window function :-
RANK() OVER (
[PARTITION BY partition_expression, ... ]
ORDER BY sort_expression [ASC | DESC], ...
)
- In
PARTITION BYclause, it divides the rows of the result set partitions to which the rank() function is applied. - In
ORDER BYThe clause specifies whether the row needs to be sorted into asc/dec order of the rows for each partition to which the Rank() function is applied.
Here is the Example of Rank()
This is the Employee Table with Name and Salary.
We are applying Rank() on Salary Column: select *, rank() over (order by salary desc) rn from Employee e;
The rank() function splits the salary column on the bases of descending order.
Now we are applying Rank() with Partition by select *, rank() over (Partition by name order by salary desc) rn from Employee e
In this Rank() function splits on the partition of the name …….