SQL Server introduced four different ranking functions either to rank records in a result-set or to rank records within groups of records of a result-set. With this inclusion we are no longer required to write several lines of code to get ranking. It does not only help in simplifying the query, but also improves the performance of the query.
The ROW_NUMBER function simply assigns sequential numbering to the records of a result-set or to the records within groups of a result-set. You can create these groupings (partition the records) using the PARTITION BY clause. The syntax for ROW_NUMBER function is:
Example: - (You can use AdventureWorks database to look at the resultset of below queries),
--This script assign sequential number to each row --of resultset which is ordered on BirthDate column
SELECT ROW_NUMBER() OVER ( ORDER BY BirthDate) AS RowNumber, LoginID, ManagerID, Title, BirthDate, MaritalStatus, Gender FROM [HumanResources].[Employee] --This script assign sequential number to each row --of resultset as it is stored in the database SELECT ROW_NUMBER() OVER ( ORDER BY (SELECT 1)) AS RowNumber, LoginID, ManagerID, Title, BirthDate, MaritalStatus, Gender FROM [HumanResources].[Employee]
The RANK function instead of assigning a sequential number to each row as in the case of the ROW_NUMBER function, it assigns rank to each record starting with 1. If it encounters two or more records to have the same ORDER BY <columns> values, it is said to be a tie and all these records get the same rank.
RANK() OVER ( [PARTITION BY partition_value_expression , ... [ n ]] ORDER BY order_value_expression , ... [ n ])
--This script assign rank to each row of result-set which is --ordered by Title column. If two or more records happen to have --same value for Title Column they will get the same rank SELECT RANK() OVER ( ORDER BY Title) AS [RecordRank], LoginID, ManagerID, Title, BirthDate, MaritalStatus, Gender FROM HumanResources.Employee