Ms SQL SERVER: ranking function details

Ranking function details
SQL Server2005 is able to rank data rows in T-SQL code. The ranking function can rank each data row, providing a way to organize output in ascending order. You can give each row a unique sequence number, or give each group of similar rows the same sequence number.

There are four types of ranking functions:
Row_number: provides continuous integer values for the query result row.
Rank: Provides ascending and non-unique ranking numbers for the row set. For rows with the same value, the same sequence number is given. Because the sequence numbers of rows have the same value, skip the sequence numbers.
Dense_rank: similar to rank. However, no matter how many flights have the same sequence number, the sequence number of each row that dense_rank puts back will increase by 1 compared with the previous one.
Ntile: place the rows obtained from the query into a group with the same (or as much as possible) number of rows and specific sequence numbers. ntile returns the sequence number of the group to which the row belongs.

The ranking function syntax is as follows:
<Function_name> () over ([partition by <partition_by_list>])
Order by <order_by_list>

Given these options in the order of options, you can see how to place options in the SELECT statement, for example:
Function_name: it can be one of row_number/rank/dense_rank/ntile.
Over: defines how rankings sort or divide data.
Partition by: defines the data used by a column as the baseline for partitioning.
Order by: defines the details of data sorting.

The following example shows four ranking functions used in the same query. For specific examples of each function, see each ranking function.
Use adventureworks;
Go
Select C. firstname, C. lastname
, Row_number () over (order by A. postalcode) as 'row number'
, Rank () over (order by A. postalcode) as 'rank'
, Dense_rank () over (order by A. postalcode) as 'dense rank'
, Ntile (4) over (order by A. posthalcode) as 'quartile'
, S. salesytd, A. postalcode
From sales. SALESPERSON s
Inner join person. Contact C
On S. salespersonid = C. contactid
Inner join person. Address
On a. addressid = C. contactid
Where territoryid is not null
And salesytd <> 0;

The following is a detailed description.

1. row_number
Description: return the serial number of an expert in the result Set Partition. The first row of each partition starts from 1.
Syntax:
Row_number () over ([<partition_by_clause>] <order_by_clause>)

Example 1: The following example returns the row_number of the salesperson in adventureworks based on the sales amount since the beginning of the year.
Use adventureworks;
Go
Select C. firstname, C. lastname
, Row_number () over (order by salesytd DESC) as 'row number'
, S. salesytd, A. postalcode
From sales. SALESPERSON s
Inner join person. Contact C
On S. salespersonid = C. contactid
Inner join person. Address
On a. addressid = C. contactid
Where territoryid is not null
And salesytd <> 0;

Note:
Order by in the over clause sorts row_number. If you add the order by clause to a select statement that uses a column other than 'row number' (or multiple columns) as the sort basis, the result set is sorted based on the order by clause.

Example 2: In the following example, rows with rows ranging from 50 to 60 (including the two rows) are returned and sorted by orderdate.
Use adventureworks;
Go
With orderedorders
(
Select salesorderid, orderdate,
Row_number () over (order by orderdate) as 'rownumber'
From sales. salesorderheader
)
Select *
From orderedorders
Where rownumber between 50 and 60;
Go

Example 3: The following example shows how to use the partition by parameter.
Use adventureworks;
Go
Select C. firstname, C. lastname
, Row_number () over
(Partition by postalcode order by salesytd DESC) as 'row number'
, S. salesytd, A. postalcode
From sales. SALESPERSON s
Inner join person. Contact C
On S. salespersonid = C. contactid
Inner join person. Address
On a. addressid = C. contactid
Where territoryid is not null
And salesytd <> 0;
Go

2. Rank
Description: return the ranking of each row in the partition of the result set. The row ranking is the plus one ranking before the related row.

Example: In the following example, products in the list are ranked by quantity. Row sets are partitioned by locationid and sorted by quantity. Note: order by in the over clause sorts rank, while order by in the SELECT statement sorts the result set.
Use adventureworks;
Go
Select I. productid, P. Name, I. locationid, I. Quantity
, Rank () over
(Partition by I. locationid order by I. Quantity DESC) as 'rank'
From production. productinventory I
Inner join production. product P
On I. productid = P. productid
Order by P. Name;
Go

3. dense_rank

4. ntile
Note: distribute rows in an ordered partition to a specified number of groups. Each group is numbered from the beginning. For each row, ntile returns the group ID of the row.

Parameters:
Integer_expression:
A positive integer constant expression that specifies the number of groups that each partition must be divided. The integer_expression type can be Int or bigint.

Note:
Integer_expression can only reference columns in the partition by clause. Integer_expression cannot reference columns listed in the current from clause.

Note:
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. For example, if the total number of rows is 53 and the number of groups is 5, each group of the first three groups contains 11 rows, and each group of the other two groups contains 10 rows. On the other hand, if the total number of rows can be fully divided by the number of groups, the number of rows will be evenly distributed between groups. For example, if the total number of rows is 50 and there are five groups, each group will contain 10 rows.

Example 1: group rows
The following example divides rows into four groups. Because the total number of rows cannot be fully divided by the number of groups, the first group contains four rows, and each other group contains three rows.
Use adventureworks;
Go
Select C. firstname, C. lastname
, Ntile (4) over (order by salesytd DESC) as 'quartile'
, S. salesytd, A. postalcode
From sales. SALESPERSON s
Inner join person. Contact C
On S. salespersonid = C. contactid
Inner join person. Address
On a. addressid = C. contactid
Where territoryid is not null
And salesytd <> 0;
Go

Example 2: Use partition by to divide the result set
The following example adds the partition by parameter to the Code in Example. Partition rows by postalcode, and divide rows into four groups in each postalcode. Note: order by in the over clause sorts ntile, while order by in the SELECT statement sorts the result set.
Use adventureworks;
Go
Select C. firstname, C. lastname
, Ntile (4) over (partition by postalcode order by salesytd DESC) as 'quartile'
, S. salesytd, A. postalcode
From sales. SALESPERSON s
Inner join person. Contact C
On S. salespersonid = C. contactid
Inner join person. Address
On a. addressid = C. contactid
Where territoryid is not null
And salesytd <> 0
Order by lastname;
Go