Ranking Functions in Sql Server 2008

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.

select *, ROW_NUMBER() over(partition by part order by value) AS Partition
from Ranker

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.

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  

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	4