SQL Server provides four ranking functions: row_number (), rank (), dense_rank (), and ntile (). The following example focuses on the use of these four functions. View data: http://www.cnblogs.com/icebutterfly/archive/2010/12/06/1897843.html
1. row_number () Returns the serial number of an expert in the result Set Partition. The first row of each partition starts from 1. The order by clause determines the order in which a unique row_number is allocated for rows in a specific partition. The following queries are sorted in reverse order based on the mathematical score:
Select ID,
Row_number () over (order by score DESC) as 'number ',
Studentid,
Classid,
Courseid,
Score
From studentscore
Where courseid = 8
2, rank () and dense_rank ()
(1 ),
Rank () function Returns the rank of each row in the partition of the result set. The row ranking is the plus one ranking before the related row. If two or more rows are associated with a ranking, each correlated row will get the same ranking select ID,
Rank () over (order by score DESC) as 'number ',
Studentid,
Classid,
Courseid,
Score
From studentscore
Where courseid = 8 note that it has similarities and differences with row_number (). You should have known that the, rank functions are similar to the row_number functions, and they are used to sort the results.
B. The difference is that the row_number function generates a unique sequence number for each value, while the rank function generates the same sequence number for the same value. (2 ),
Dense_rank () function Returns the row ranking in the result set partition, without any interruption in the ranking. The row ranking is equal to the number of all the rankings before the discussed row plus one. If two or more rows are subject to the ranking constraints in the same partition, each row will receive the same rank select ID,
Dense_rank () over (order by score DESC) as 'number ',
Studentid,
Classid,
Courseid,
Score
From studentscore
Where courseid = 8 the numbers of students with 86 points are 3 And the serial numbers below them are 4 (That is to say, the number of the dense_rank () function query is as continuous as row_number (), but the same sequence number is generated for rows with the same value. In this regard, for queries with the same query conditions and sorting conditions, the result set of the row_number () function query is a subset of the results of the dense_rank () function query ). This is the biggest difference between rank and dense_rank.
3. ntile () The ntile function associates rows in the result to a group and assigns each row a group number from the beginning. For each row, ntile returns the group ID of the row.
If the number of rows in a partition cannot be divisible by integer_expression, a Member may have two groups of different sizes. In the order specified by the over clause, a large group is placed before a small group. Select ID,
Ntile (6) over (order by classid DESC) as 'group number ',
Studentid,
Classid,
Courseid,
Score
From studentscore
Where courseid = 8
