Ranking Functions

May 31, 2009

Introduction

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. Ranking functions are nondeterministic.

Transact-SQL provides the following ranking functions:

RANK :The rank of a row is one plus the number of ranks that come before the row in question.

NTILE :The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.

DENSE_RANK :The rank of a row is one plus the number of distinct ranks that come before the row in question.

ROW_NUMBER : Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

Example

 

USE AdventureWorks;

GO

SELECT c.FirstName, c.LastName

      –Each Row Take row number based on postal code

    ,ROW_NUMBER() OVER (ORDER BY a.PostalCode) AS ‘Row Number’

    –Each same postalcode take same rank then next postalcode take (Total count of same PostalCode rows)+1

    ,RANK() OVER (ORDER BY a.PostalCode) AS ‘Rank’

    –Each same postalcode take same rank then next postalcode take rank+1

    ,DENSE_RANK() OVER (ORDER BY a.PostalCode) AS ‘Dense Rank’

    –divide Total Result Sets to 4 groups

    ,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;

result will be

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