RSS

Ranking Functions

29 Jul

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 P
ostalCode
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
Advertisements
 

Tags: , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: