T-SQL RANK() , DENSE_RANK() , NTILE(), ROW_NUMBER()


Rank():
Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question.
Usage: RANK ( )    OVER ( [ < partition_by_clause > ] < order_by_clause > )  

Dense_Rank() :
Returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question.
Usage: DENSE_RANK ( )    OVER ( [ < partition_by_clause > ] < order_by_clause > )  

Ntile():
Distributes the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.
Usage: NTILE (integer_expression)    OVER ( [ <partition_by_clause> ] < order_by_clause > )  

Row_Number():
Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
ROW_NUMBER ( )     OVER ( [ <partition_by_clause> ] <order_by_clause> )  

Example  

All Recordset :   

select * from finals 
RecordID Name Surname Course Point
1 Bugra Postaci Mathematics 89
2 Bugra Postaci Lecture 90
3 Dany Lowe Mathematics 75
4 Dany Lowe Lecture 85
5 Alice Marcel Mathematics 77
6 Alice Marcel Lecture 100
7 Simon Duru Mathematics 45
8 Simon Duru Lecture 58

 

select row_number() over(order by RecordID desc) as RowNumber ,* from finals 

RowNumber RecordID Name Surname Course Point
1 8 Simon Duru Lecture 58
2 7 Simon Duru Mathematics 45
3 6 Alice Marcel Lecture 100
4 5 Alice Marcel Mathematics 77
5 4 Dany Lowe Lecture 85
6 3 Dany Lowe Mathematics 75
7 2 Bugra Postaci Lecture 90
8 1 Bugra Postaci Mathematics 89

 

select ntile(2) over(order by RecordID desc) as [Ntile] ,* from finals 

Ntile RecordID Name Surname Course Point
1 8 Simon Duru Lecture 58
1 7 Simon Duru Mathematics 45
1 6 Alice Marcel Lecture 100
1 5 Alice Marcel Mathematics 77
2 4 Dany Lowe Lecture 85
2 3 Dany Lowe Mathematics 75
2 2 Bugra Postaci Lecture 90
2 1 Bugra Postaci Mathematics 89

 

as you see ntile function just divide the scope with given number 

select rank() over (order by Surname) as [Rank], * from finals 

Rank RecordID Name Surname Course Point
1 7 Simon Duru Mathematics 45
1 8 Simon Duru Lecture 58
3 3 Dany Lowe Mathematics 75
3 4 Dany Lowe Lecture 85
5 5 Alice Marcel Mathematics 77
5 6 Alice Marcel Lecture 100
7 1 Bugra Postaci Mathematics 89
7 2 Bugra Postaci Lecture 90

 
rank function is working like  match one “Duru” as 1 match another “Duru” as 1 but total count as 2 , next surname matches “Lowe” as count +1 as 3 , another “Lowe” as 3  ; now total count is 4 matching next surname as “Marcel” is count + 1 as 5 and goes on …. 

select dense_rank() over (order by Surname) as [DenseRank], * from finals 

DenseRank RecordID Name Surname Course Point
1 7 Simon Duru Mathematics 45
1 8 Simon Duru Lecture 58
2 3 Dany Lowe Mathematics 75
2 4 Dany Lowe Lecture 85
3 5 Alice Marcel Mathematics 77
3 6 Alice Marcel Lecture 100
4 1 Bugra Postaci Mathematics 89
4 2 Bugra Postaci Lecture 90

 Dense rank its obvious like shown. 

 

if you want to make your denserank number reseting by groups there is a way use PARTITION BY
below example reseting ranks by using “partition by” by course column
select
dense_rank() over (Partition by course order by Surname) as [DenseRank], * from finals 
 

DenseRank RecordID Name Surname Course Point
1 8 Simon Duru Lecture 58
2 4 Dany Lowe Lecture 85
3 6 Alice Marcel Lecture 100
4 2 Bugra Postaci Lecture 90
1 7 Simon Duru Mathematics 45
2 3 Dany Lowe Mathematics 75
3 5 Alice Marcel Mathematics 77
4 1 Bugra Postaci Mathematics 89

Thats all folks… 

Advertisements

About bpostaci
Sr. Support Escalation Engineer in Microsoft.

One Response to T-SQL RANK() , DENSE_RANK() , NTILE(), ROW_NUMBER()

  1. Pingback: SQL-Server – RANK et ROW_NUMBER | kerrubin's blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: