ROW_NUMBER
This function return the sequantial number of the rows based on the order clause.
RANK
This function return the rank of a row over a partition of a result set.
DENSE_RANK
This function return the rank of a row over a partition of a result set with out any gaps in the ranking.
NTILE
This function assign the rank number to each record present in a partition into a specified number of groups.
Examples
select * from Employee
select * ,
row_number() over (order by Salary desc) as [Row_number],
rank() over (order by Salary desc) as [Rank],
dense_rank() over (order by Salary desc) as [Dense_rank],
ntile(3) over (order by Salary desc) as [Ntile]
from Employee
select * ,
row_number() over (partition by Department order by Salary desc) as [Row_number],
rank() over (partition by Department order by Salary desc) as [Rank],
dense_rank() over (partition by Department order by Salary desc) as [Dense_rank],
ntile(3) over (partition by Department order by Salary desc) as [Ntile]
from Employee
