通过

Ranking Functions in SQL Server

  • 项目

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.

ROW_NUMBER()

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:

ROW_NUMBER

() OVER ( [PARTITION BY partition_value_expression , ... [ n ]] ORDER BY order_value_expression , ... [ n ])

  • OVER clause is required in all the ranking functions and with that you specify the partitioning and ordering of records before the ranking functions are evaluated. If you don't specify it, you will get an error similar to "Incorrect syntax near 'ROW_NUMBER', expected 'OVER'."
  • PARTITION BY clause is not mandatory and if you don't specify it all the records of the result-set will be considered as a part of single record group or a single partition and then ranking functions are applied. When you specify a column/set of columns with PARTITION BY clause then it will divide the result-set into record groups/partitions and then finally ranking functions are applied to each record group/partition separately and the rank will restart from 1 for each record group/partition separately.
  • ORDER BY clause is mandatory and if you don't specify it you will get an error similar to "The ranking function "ROW_NUMBER" must have an ORDER BY clause.". With this clause you specify a column or a set of columns which will be used to order the records within a result-set or within record groups/partitions of a result-set. Please note you can specify only those columns which are being made available by the FROM clause of the query. Also, you can not specify an integer to represent the position of a column, if you do so you will get "Windowed functions do not support integer indices as ORDER BY clause expressions.

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]  

RANK()

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