SQL Server ranking or sorting functions

SQL Server has the following functions for ranking or sorting: 1, Rank: The position of each record in the result set, but the ranking may be discontinuous, for example: if there are two first in the same group, the next position in the group jumps directly to the third place.

Select *,rank () over (partition by ModifiedDate order by LocationID) as Rank from Production.productinventory

2, Dense_rank: function and Rank similar. But the number of rankings is continuous, for example: if there are two first in the same group, then the next place in the group is second

3, Row_number: According to the group shows each record in the group appears in the position, such as: if there are two first. These two first names are ranked 1, 2 in a group, and the next group is still sorted from 1, and so on.

Select *,row_number () over (partition by ProductID  

4. NTILE: Partitions The result set according to the specified number of groupings and records its position in the group.

Ability to sort the data of the result set. Divide the result set into n groups according to the specified quantity, and give each group a group number, grouped in a very easy way, the total number of records of the result set divided by N, if the number of M, the first M group will add a record, therefore, not all groups have the same number of records. However, a multi-record group has only one record.

Select Top *,ntile (2) over (partition by ProductID  

The above SQL statements are debugged under SQL Server 2008. Using AdventureWorks

The four function syntax formats are also matched with the over clause, and the over clause is defined in the form of a specified shard or sort record, such as the following: Over ("Partition by < provides a descriptive description of the value >" ORDER by < Field > [des| ASC]) The Partition by clause determines how the rank records are grouped, and the ORDER BY clause determines how each group of records is sorted, assuming that the Partition by clause is omitted, all records are a set

SQL Server ranking or sorting functions