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

Test

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 :   

1. 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

1. 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

1. 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

1. 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 ….

1. 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

1. 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…

http://blog.bugrapostaci.com/2010/01/26/t-sql-rank-dense_rank-ntile-row_number/