Export (0) Print
Expand All

Ranking Functions (Transact-SQL)

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:

The following shows the four ranking functions used in the same query. For function specific examples, see each ranking function.

USE AdventureWorks2008R2;
GO
SELECT p.FirstName, p.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 AS s 
    INNER JOIN Person.Person AS p 
        ON s.BusinessEntityID = p.BusinessEntityID
    INNER JOIN Person.Address AS a 
        ON a.AddressID = p.BusinessEntityID
WHERE TerritoryID IS NOT NULL 
    AND SalesYTD <> 0;

Here is the result set.

FirstName

LastName

Row Number

Rank

Dense Rank

Quartile

SalesYTD

PostalCode

Michael

Blythe

1

1

1

1

4557045.0459

98027

Linda

Mitchell

2

1

1

1

5200475.2313

98027

Jillian

Carson

3

1

1

1

3857163.6332

98027

Garrett

Vargas

4

1

1

1

1764938.9859

98027

Tsvi

Reiter

5

1

1

2

2811012.7151

98027

Shu

Ito

6

6

2

2

3018725.4858

98055

José

Saraiva

7

6

2

2

3189356.2465

98055

David

Campbell

8

6

2

3

3587378.4257

98055

Tete

Mensa-Annan

9

6

2

3

1931620.1835

98055

Lynn

Tsoflias

10

6

2

3

1758385.926

98055

Rachel

Valdez

11

6

2

4

2241204.0424

98055

Jae

Pak

12

6

2

4

5015682.3752

98055

Ranjit

Varkey Chudukatil

13

6

2

4

3827950.238

98055

Community Additions

ADD
Show:
© 2014 Microsoft