Monday, July 5, 2021

Ranking Functions in SQL Server

 SQL Server Ranking Function:

Ranking functions return a ranking value for each row in a partition. Depending on the function that is used, some rows might receive the same value as other rows.

Click on the link above to see each ranking function with example.
Below example demonstrates the four ranking functions in one go.

Example:
SelesPerson Table

FirstName

LastName

SalesYTD

PostalCode

Michael

Blythe

3763178.179

98027

Linda

Mitchell

4251368.55

98027

Jillian

Carson

3189418.366

98027

Garrett

Vargas

1453719.465

98027

Tsvi

Reiter

2315185.611

98027

Pamela

Ansman-Wolfe

1352577.133

98027

Shu

Ito

2458535.617

98055

José

Saraiva

2604540.717

98055

David

Campbell

1573012.938

98055

Tete

Mensa-Annan

1576562.197

98055

Lynn

Tsoflias

1421810.924

98055

Rachel

Valdez

1827066.712

98055

Jae

Pak

4116871.228

98055

Ranjit

Varkey Chudukatil

3121616.32

98055




SELECT FirstName, LastName  
    ,ROW_NUMBER() OVER (ORDER BY PostalCode) AS "Row Number"  
    ,RANK() OVER (ORDER BY PostalCode) AS Rank  
    ,DENSE_RANK() OVER (ORDER BY PostalCode) AS "Dense Rank"  
    ,NTILE(4) OVER (ORDER BY PostalCode) AS Quartile  
    ,SalesYTD  
    ,PostalCode  
FROM SalesPerson
Output:

FirstName

LastName

Row Number

Rank

Dense Rank

Quartile

SalesYTD

PostalCode

Michael

Blythe

1

1

1

1

3763178

98027

Linda

Mitchell

2

1

1

1

4251369

98027

Jillian

Carson

3

1

1

1

3189418

98027

Garrett

Vargas

4

1

1

1

1453719

98027

Tsvi

Reiter

5

1

1

2

2315186

98027

Pamela

Ansman-Wolfe

6

1

1

2

1352577

98027

Shu

Ito

7

7

2

2

2458536

98055

José

Saraiva

8

7

2

2

2604541

98055

David

Campbell

9

7

2

3

1573013

98055

Tete

Mensa-Annan

10

7

2

3

1576562

98055

Lynn

Tsoflias

11

7

2

3

1421811

98055

Rachel

Valdez

12

7

2

4

1827067

98055

Jae

Pak

13

7

2

4

4116871

98055

Ranjit

Varkey Chudukatil

14

7

2

4

3121616

98055


SQL Server RANK() Function with example

 Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question.

ROW_NUMBER and RANK are similar. ROW_NUMBER numbers all rows sequentially (for example 1, 2, 3, 4, 5). RANK provides the same numeric value for ties (for example 1, 2, 2, 4, 5).

Syntax:

RANK ( ) OVER ( [ partition_by_clause ] order_by_clause )


Example:

Table: SalesPerson

FirstName

LastName

TerritoryName

Lynn

Tsoflias

Australia

José

Saraiva

Canada

Garrett

Vargas

Canada

Jillian

Carson

Central

Ranjit

Varkey

France

Rachel

Valdez

Germany

Michael

Blythe

Northeast

Tete

Mensa-Annan

Northwest

David

Campbell

Northwest

Pamela

Ansman-Wolfe

Northwest

Tsvi

Reiter

Southeast

Linda

Mitchell

Southwest

Shu

Ito

Southwest

Jae

Pak

United Kingdom


Returning Row Number:

SELECT RANK() OVER(ORDER BY TerritoryName) AS Row,   
    FirstName, LastName,TerritoryName
FROM SalesPerson
Output:

ROW

FirstName

LastName

TerritoryName

1

Lynn

Tsoflias

Australia

2

Garrett

Vargas

Canada

2

José

Saraiva

Canada

4

Jillian

Carson

Central

5

Ranjit

Varkey

France

6

Rachel

Valdez

Germany

7

Michael

Blythe

Northeast

7

Tete

Mensa-Annan

Northwest

7

David

Campbell

Northwest

7

Pamela

Ansman-Wolfe

Northwest

11

Tsvi

Reiter

Southeast

11

Linda

Mitchell

Southwest

11

Shu

Ito

Southwest

14

Jae

Pak

United Kingdom


Using ROW_NUMBER() with PARTITION

USE AdventureWorks2017;
GO  
SELECT i.ProductID, p.Name, i.LocationID, i.Quantity  
    ,RANK() OVER   
    (PARTITION BY i.LocationID ORDER BY i.Quantity) AS Rank  
FROM Production.ProductInventory AS i   
INNER JOIN Production.Product AS p   
    ON i.ProductID = p.ProductID  
WHERE i.LocationID BETWEEN 3 AND 4  
ORDER BY i.LocationID; 
Output:

ProductID

Name

LocationID

Quantity

Rank

492

Paint - Black

3

17

1

496

Paint - Yellow

3

30

2

493

Paint - Red

3

41

3

494

Paint - Silver

3

49

4

495

Paint - Blue

3

49

4

494

Paint - Silver

4

12

1

492

Paint - Black

4

14

2

493

Paint - Red

4

24

3

496

Paint - Yellow

4

25

4

495

Paint - Blue

4

35

5