SQL Server ranking function

Row_number (): indicates the number of rows in the overall result set, and Adds 1 in sequence.

Rank (): group (or not group) first, then each group of rows indicates the serial number, and Add 1 in sequence. If the value of the sequence in this group is the same, some rows have the same sequence number, but the sequence numbers of the following rows are inconsistent. For example, if the serial number of two rows is 1st, the serial number of the fifth row is 3 instead of 2.

Dense_rank (): close to rank (). The difference is that although there are the same serial numbers, the ranking is consistent. For example, if the serial number of two rows is 1st, the serial number of the fifth row is 2 instead of 3.

Ntile (n): divides the result set into N equal portions, and then marks the sequence number for each row in each equal portion. The sequence numbers in the same portion are the same (for example, divided into 4 equal portions, the sequence numbers of all rows in part 1 are 1, and the numbers of all rows in Part 2 are 2, and so on); or group first, then each group is n equal portions, and the sequence numbers are marked in the equal portions. (Under what circumstances will it be used ?)

 

 

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.PostalCode) AS 'Quartile'
    ,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s 
    INNER JOIN Person.Contact c ON s.SalesPersonID = c.ContactID
    INNER JOIN Person.Address a ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0;

Firstname	Lastname	Row number	Rank	Dense rank	Quartile	Salesytd	Postalcode
Maciej	Dusza	1	1	1	1	4557045	98027
Shelley	Dyck	2	1	1	1	5200475	98027
Linda	Ecoffey	3	1	1	1	3857164	98027
Carla	Eldridge	4	1	1	1	1764939	98027
Carol	Elliott	5	1	1	2	2811013	98027
Jauna	Elson	6	6	2	2	3018725	98055
Michael	Emanuel	7	6	2	2	3189356	98055
Terry	Eminhizer	8	6	2	3	3587378	98055
Gail	Erickson	9	6	2	3	5015682	98055
Mark	Erickson	10	6	2	3	3827950	98055
Martha	Espinoza	11	6	2	4	1931620	98055
Janeth	Esteves	12	6	2	4	2241204	98055
Twanna	Evans	13	6	2	4	1758386	98055