70-457 Reviewer #01, Rank Functions

The rank functions in T-SQL was introduced in SQL Server 2005 and nothing much has changed about its behavior up to now with SQL Server 2012. You use ranking functions to return a ranking value for each row within a partition. For the EXAM 70-457, there are about 3-4 questions related to the ranking functions thus i decided to add this to my review even I am already familiar with its usage.

Much of what has been explained here in MSDN is also very straightforward and it includes some queries to try from the AdventureWorks2012 database.

There are 4 rank functions in SQL Server;

ROW_NUMBER() – This is the simplest function among the four. It just gives out consecutive numbering of all the rows in the result set by the order selected in the OVER clause for each partition specified in the OVER clause, unless partition is specified.

RANK() – In case that there is two rows with the same row number,  this function is used when you want all tied rows to have the same ranking, but the next row after the tied rows to have the rank it would have been assigned if there had been no tie. In other words, the numbers assigned by RANK are not necessarily consecutive.

From the MSDN, using a sample query from AdventureWorks2012, an example below

USE AdventureWorks2012;
GO
SELECT i.ProductID, p.Name, i.LocationID, i.Quantity
    ,RANK() OVER 
    (PARTITION BY i.LocationID ORDER BY i.Quantity DESC) 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;
GO

ProductID   Name                   LocationID   Quantity Rank
----------- ---------------------- ------------ -------- ----
494         Paint - Silver         3            49       1
495         Paint - Blue           3            49       1
493         Paint - Red            3            41       3
496         Paint - Yellow         3            30       4
492         Paint - Black          3            17       5
495         Paint - Blue           4            35       1
496         Paint - Yellow         4            25       2
493         Paint - Red            4            24       3
492         Paint - Black          4            14       4
494         Paint - Silver         4            12       5
 (10 row(s) affected)

Notice that ProductID 494 and 495 both have the same quantity hence, their rank is both 1. ProductID 493 has the rank 3 because ProductID 495 would have been rank 2 had it not been tied with ProductID 494.

DENSE_RANK() – This function is similar to RANK() function but it returns the correct ranking without any gaps in between. From the above example, the ProductID 493 would have been rank 2.

Using the sample query from MSDN,

USE AdventureWorks2012;
GO
SELECT i.ProductID, p.Name, i.LocationID, i.Quantity
    ,DENSE_RANK() OVER 
    (PARTITION BY i.LocationID ORDER BY i.Quantity DESC) 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;
GO

ProductID   Name                               LocationID Quantity Rank
----------- ---------------------------------- ---------- -------- ----
494         Paint - Silver                     3          49       1
495         Paint - Blue                       3          49       1
493         Paint - Red                        3          41       2
496         Paint - Yellow                     3          30       3
492         Paint - Black                      3          17       4
495         Paint - Blue                       4          35       1
496         Paint - Yellow                     4          25       2
493         Paint - Red                        4          24       3
492         Paint - Black                      4          14       4
494         Paint - Silver                     4          12       5

(10 row(s) affected)

NTILE – This function evenly distributes all rows in a partition. To better illustrate, from MSDN;

USE AdventureWorks2012; 
GO
SELECT p.FirstName, p.LastName
    ,NTILE(4) OVER(ORDER BY SalesYTD DESC) AS Quartile
    ,CONVERT(nvarchar(20),s.SalesYTD,1) AS 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;
GO

FirstName      LastName              Quartile  SalesYTD       PostalCode
-------------  --------------------- --------- -------------- ---------
Linda          Mitchell              1         4,251,368.55   98027
Jae            Pak                   1         4,116,871.23   98055
Michael        Blythe                1         3,763,178.18   98027
Jillian        Carson                1         3,189,418.37   98027
Ranjit         Varkey Chudukatil     2         3,121,616.32   98055
José           Saraiva               2         2,604,540.72   98055
Shu            Ito                   2         2,458,535.62   98055
Tsvi           Reiter                2         2,315,185.61   98027
Rachel         Valdez                3         1,827,066.71   98055
Tete           Mensa-Annan           3         1,576,562.20   98055
David          Campbell              3         1,573,012.94   98055
Garrett        Vargas                4         1,453,719.47   98027
Lynn           Tsoflias              4         1,421,810.92   98055
Pamela         Ansman-Wolfe          4         1,352,577.13   98027
 (14 row(s) affected)

See that all the rows are evenly distributed into 4 groups and ranked based on their SalesYTD, Basing  on the total number of rows for the result set,  notice that the first 2 groups has 4 employees each and the last 2 groups have 3 employees each. So there.

Based from previous certification exams i took, I’m thinking the questions would go a lot like there’s 3 columns, I want to get the ranks in sequential order or ranks with no gaps in between. The choices presented can be a little tricky and confusing so carefully read each option before finalizing your choice.

Happy weekend everyone!

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

SHE GOES GLOBAL

Travel the world, change your life.

My Time to Travel

The travels of an old(er), solo, woman

Blog Home for MSSQLDUDE

The life of a data geek

unfoldthecreativity

Traveller Observer

The SQL Pro

(Ayman El-Ghazali - www.thesqlpro.com)

Meels on Wheels

Meels for breakfast, lunch and dinner.

juliansherman.net/

Building A Business While Having A Life

Paul Turley's SQL Server BI Blog

sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009

TroubleshootingSQL

Explaining the bits and bytes of SQL Server and Azure

Coding Tips

We are crazy about programming and we want to share our craziness with you!!!

SQL Studies

 Live, Learn, Share

Sql And Me

My Experiments with SQLServer

Dimitrios Kalemis

I am exactly like Jesus Christ: an atheist and anarchist against society and bad people with influence and power (judges, social workers, politicians, priests, and teachers).

Clint Huijbers' Blog

Senior Certified Microsoft BI Consultant

Blog of Many Useless Wonders

Where Uselessness Abounds!

Steve Spevack's Blog

IT Recruitment