Ranking Functions in Sql Server 2008
Ranking functions are the function which are used to order the numbers, to partition the result set, to arrange the number in sequence, to select the particular row with specific condition and to solve complex sql problems in easy manner.
Ranking functions are the function which are used to order the numbers, to partition the result set, to arrange the number in sequence, to select the particular row with specific condition and to solve complex sql problems in easy manner.
There are four ranking functions in sql server.
They are Row_Number(), Rank(), Dense_Rank(), Ntile().Row_Number()
Row_Number function gives the sequential order of numbers starting from 1 for the specified row. This function is used to split the table based on this row number.Rank()
Rank() function consider the repeating values in the column. If two values are same in the column means both values are given the same rank and the next value gets the respected rank.Dense_Rank()
Dense_Rank() is as same as rank function the only difference is the next value to the repeating value should take the rank in sequence order.Ntile()
Ntile() function is different from others. It splits the table based on the given range and rank the number for the partitions.
For Example consider this table
create table Ranker(value int)
Insert into Ranker values(8),(1),(6),(2),(3),(6),(4),(5),(8),(9),(3),(7)
select *,
ROW_NUMBER() over(order by value) AS Row_No,
RANK() over(order by value) AS Rank ,
DENSE_RANK() over(order by value) AS Dense_Rank,
NTILE(4) over(order by value) AS Ntile
from Ranker Output
value Row_No Rank D_Rank Ntile
1 1 1 1 1
2 2 2 2 1
3 3 3 3 1
3 4 3 3 2
4 5 5 4 2
5 6 6 5 2
6 7 7 6 3
6 8 7 6 3
7 9 9 7 3
8 10 10 8 4
8 11 10 8 4
9 12 12 9 4Partition and Rank Function:
Partition function may be defined as second sorting. It acts like a Group by function.
select *, ROW_NUMBER() over(partition by part order by value) AS Partition
from Ranker Output
value part Partition
2 1 1
3 1 2
3 1 3
5 1 4
1 2 1
4 2 2
8 2 3
6 3 1
8 3 2
6 4 1
7 4 2
9 4 3