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/