Ranking Functions (Transact-SQL): NTILE, RANK, DENSE_RANK, ROW_NUMBER

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
Advertisements

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