SQL Server has the following functions for ranking or sorting: 1, Rank: The position of each record in the result set, but the rank may be discontinuous, for example: if there are two first names within 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 is similar to rank, but the number of rankings is continuous, for example: if there are two first in the same group, then the next rank in the group is the second place
3, Row_number: According to the group shows each record in this group appears in the position, for example: if there are two first, then the first place in a group ranked 1, 2, the next group of the order is still starting 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. After sorting the result set's data, dividing the result set into n groups according to the specified number, and giving each group a group number, it is easy to group the total number of records of the result set by N, and if there are more than M, the first M group will have a record number, so not all groups have the same record count. However, there is at most one record for a multi-logged group.
Select Top *,ntile (2) over (partition by ProductID
The above SQL statements are debugged under SQL Server 2008, using the AdventureWorks
The four function syntax formats are the same, with the over clause, and the over clause is defined in the form of a specified shard or sort record: Over ("Partition by < provides a 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 ordered, and if the Partition by clause is omitted, all are recorded as a set of
Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.
SQL Server ranking or sorting functions
