T-SQL RANK,Row_Number,NTile and Dense_Rank

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…


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