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

26/01/2010 Leave a comment

**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 :

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 |

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 |

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 |

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

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…