AdventureWorks Database: Using rank function with order by clause
SQL Query - AdventureWorks: Exercise-34 with Solution
34. From the following table write a query in SQL to find those persons who lives in a territory and the value of salesytd except 0. Return first name, last name,row number as 'Row Number', 'Rank', 'Dense Rank' and NTILE as 'Quartile', salesytd and postalcode. Order the output on postalcode column.
Sample table: Sales.SalesPerson
businessentityid|territoryid|salesquota|bonus|commissionpct|salesytd |saleslastyear|rowguid |modifieddate |
----------------+-----------+----------+-----+-------------+------------+-------------+------------------------------------+-----------------------+
274| | | 0| 0| 559697.5639| 0|48754992-9ee0-4c0e-8c94-9451604e3e02|2010-12-28 00:00:00.000|
275| 2| 300000| 4100| 0.012|3763178.1787| 1750406.4785|1e0a7274-3064-4f58-88ee-4c6586c87169|2011-05-24 00:00:00.000|
276| 4| 250000| 2000| 0.015|4251368.5497| 1439156.0291|4dd9eee4-8e81-4f8c-af97-683394c1f7c0|2011-05-24 00:00:00.000|
277| 3| 250000| 2500| 0.015|3189418.3662| 1997186.2037|39012928-bfec-4242-874d-423162c3f567|2011-05-24 00:00:00.000|
278| 6| 250000| 500| 0.01|1453719.4653| 1620276.8966|7a0ae1ab-b283-40f9-91d1-167abf06d720|2011-05-24 00:00:00.000|
279| 5| 300000| 6700| 0.01| 2315185.611| 1849640.9418|52a5179d-3239-4157-ae29-17e868296dc0|2011-05-24 00:00:00.000|
280| 1| 250000| 5000| 0.01|1352577.1325| 1927059.178|be941a4a-fb50-4947-bda4-bb8972365b08|2011-05-24 00:00:00.000|
281| 4| 250000| 3550| 0.01|2458535.6169| 2073505.9999|35326ddb-7278-4fef-b3ba-ea137b69094e|2011-05-24 00:00:00.000|
282| 6| 250000| 5000| 0.015|2604540.7172| 2038234.6549|31fd7fc1-dc84-4f05-b9a0-762519eacacc|2011-05-24 00:00:00.000|
283| 1| 250000| 3500| 0.012|1573012.9383| 1371635.3158|6bac15b2-8ffb-45a9-b6d5-040e16c2073f|2011-05-24 00:00:00.000|
284| 1| 300000| 3900| 0.019|1576562.1966| 0|ac94ec04-a2dc-43e3-8654-dd0c546abc17|2012-09-23 00:00:00.000|
285| | | 0| 0| 172524.4512| 0|cfdbef27-b1f7-4a56-a878-0221c73bae67|2013-03-07 00:00:00.000|
286| 9| 250000| 5650| 0.018|1421810.9242| 2278548.9776|9b968777-75dc-45bd-a8df-9cdaa72839e1|2013-05-23 00:00:00.000|
287| | | 0| 0| 519905.932| 0|1dd1f689-df74-4149-8600-59555eef154b|2012-04-09 00:00:00.000|
288| 8| 250000| 75| 0.018|1827066.7118| 1307949.7917|224bb25a-62e3-493e-acaf-4f8f5c72396a|2013-05-23 00:00:00.000|
289| 10| 250000| 5150| 0.02|4116871.2277| 1635823.3967|25f6838d-9db4-4833-9ddc-7a24283af1ba|2012-05-23 00:00:00.000|
290| 7| 250000| 985| 0.016|3121616.3202| 2396539.7601|f509e3d4-76c8-42aa-b353-90b7b8db08de|2012-05-23 00:00:00.000|
Sample table: Person.Person
businessentityid|persontype|namestyle|title|firstname |middlename |lastname |suffix|emailpromotion|additionalcontactinfo|demographics|rowguid |modifieddate |
----------------+----------+---------+-----+------------------------+----------------+----------------------+------+--------------+---------------------+------------+------------------------------------+-----------------------+
1|EM |false | |Ken |J |Sánchez | | 0| |[XML] |92c4279f-1207-48a3-8448-4636514eb7e2|2009-01-07 00:00:00.000|
2|EM |false | |Terri |Lee |Duffy | | 1| |[XML] |d8763459-8aa8-47cc-aff7-c9079af79033|2008-01-24 00:00:00.000|
3|EM |false | |Roberto | |Tamburello | | 0| |[XML] |e1a2555e-0828-434b-a33b-6f38136a37de|2007-11-04 00:00:00.000|
4|EM |false | |Rob | |Walters | | 0| |[XML] |f2d7ce06-38b3-4357-805b-f4b6b71c01ff|2007-11-28 00:00:00.000|
5|EM |false |Ms. |Gail |A |Erickson | | 0| |[XML] |f3a3f6b4-ae3b-430c-a754-9f2231ba6fef|2007-12-30 00:00:00.000|
6|EM |false |Mr. |Jossef |H |Goldberg | | 0| |[XML] |0dea28fd-effe-482a-afd3-b7e8f199d56f|2013-12-16 00:00:00.000|
7|EM |false | |Dylan |A |Miller | | 2| |[XML] |c45e8ab8-01be-4b76-b215-820c8368181a|2009-02-01 00:00:00.000|
8|EM |false | |Diane |L |Margheim | | 0| |[XML] |a948e590-4a56-45a9-bc9a-160a1cc9d990|2008-12-22 00:00:00.000|
9|EM |false | |Gigi |N |Matthew | | 0| |[XML] |5fc28c0e-6d36-4252-9846-05caa0b1f6c5|2009-01-09 00:00:00.000|
-- more --
Sample table: Person.Address
businessentityid|persontype|namestyle|title|firstname |middlename |lastname |suffix|emailpromotion|additionalcontactinfo|demographics|rowguid |modifieddate |
----------------+----------+---------+-----+------------------------+----------------+----------------------+------+--------------+---------------------+------------+------------------------------------+-----------------------+
1|EM |false | |Ken |J |Sánchez | | 0| |[XML] |92c4279f-1207-48a3-8448-4636514eb7e2|2009-01-07 00:00:00.000|
2|EM |false | |Terri |Lee |Duffy | | 1| |[XML] |d8763459-8aa8-47cc-aff7-c9079af79033|2008-01-24 00:00:00.000|
3|EM |false | |Roberto | |Tamburello | | 0| |[XML] |e1a2555e-0828-434b-a33b-6f38136a37de|2007-11-04 00:00:00.000|
4|EM |false | |Rob | |Walters | | 0| |[XML] |f2d7ce06-38b3-4357-805b-f4b6b71c01ff|2007-11-28 00:00:00.000|
5|EM |false |Ms. |Gail |A |Erickson | | 0| |[XML] |f3a3f6b4-ae3b-430c-a754-9f2231ba6fef|2007-12-30 00:00:00.000|
6|EM |false |Mr. |Jossef |H |Goldberg | | 0| |[XML] |0dea28fd-effe-482a-afd3-b7e8f199d56f|2013-12-16 00:00:00.000|
7|EM |false | |Dylan |A |Miller | | 2| |[XML] |c45e8ab8-01be-4b76-b215-820c8368181a|2009-02-01 00:00:00.000|
8|EM |false | |Diane |L |Margheim | | 0| |[XML] |a948e590-4a56-45a9-bc9a-160a1cc9d990|2008-12-22 00:00:00.000|
9|EM |false | |Gigi |N |Matthew | | 0| |[XML] |5fc28c0e-6d36-4252-9846-05caa0b1f6c5|2009-01-09 00:00:00.000|
-- more --
Sample Solution:
-- Selecting specific columns and calculated values
SELECT p.FirstName, p.LastName
-- Calculating row number for each row based on PostalCode order
,ROW_NUMBER() OVER (ORDER BY a.PostalCode) AS "Row Number"
-- Calculating rank for each row based on PostalCode order
,RANK() OVER (ORDER BY a.PostalCode) AS "Rank"
-- Calculating dense rank for each row based on PostalCode order
,DENSE_RANK() OVER (ORDER BY a.PostalCode) AS "Dense Rank"
-- Dividing the result set into quartiles based on PostalCode order
,NTILE(4) OVER (ORDER BY a.PostalCode) AS "Quartile"
-- Including sales YTD and PostalCode columns
,s.SalesYTD, a.PostalCode
-- From the SalesPerson table alias s, joining with Person table alias p
FROM Sales.SalesPerson AS s
INNER JOIN Person.Person AS p
ON s.BusinessEntityID = p.BusinessEntityID
-- Joining with Address table alias a
INNER JOIN Person.Address AS a
ON a.AddressID = p.BusinessEntityID
-- Filtering the results to include only rows where TerritoryID is not NULL
-- and SalesYTD is not equal to 0
WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0;
Explanation:
- The SQL query selects columns from the Person table, including calculated values.
- It calculates various ranking metrics using window functions like ROW_NUMBER, RANK, DENSE_RANK, and NTILE.
- The ROW_NUMBER function assigns a unique sequential number to each row.
- The RANK function assigns a rank to each row, with tied values getting the same rank and subsequent ranks skipping.
- The DENSE_RANK function assigns a rank to each row, with tied values getting the same rank and subsequent ranks not skipping.
- The NTILE function divides the result set into a specified number of groups (in this case, quartiles).
- It then joins tables SalesPerson, Person, and Address using inner joins based on the respective keys.
- The WHERE clause filters the rows to include only those where TerritoryID is not NULL and SalesYTD is not equal to 0.
Sample Output:
firstname|lastname |Row Number|Rank|Dense Rank|Quartile|salesytd |postalcode| ---------+-----------------+----------+----+----------+--------+------------+----------+ Michael |Blythe | 1| 1| 1| 1|3763178.1787|98027 | Linda |Mitchell | 2| 1| 1| 1|4251368.5497|98027 | Jillian |Carson | 3| 1| 1| 1|3189418.3662|98027 | Garrett |Vargas | 4| 1| 1| 1|1453719.4653|98027 | Tsvi |Reiter | 5| 1| 1| 2| 2315185.611|98027 | Pamela |Ansman-Wolfe | 6| 1| 1| 2|1352577.1325|98027 | Shu |Ito | 7| 7| 2| 2|2458535.6169|98055 | José |Saraiva | 8| 7| 2| 2|2604540.7172|98055 | David |Campbell | 9| 7| 2| 3|1573012.9383|98055 | Tete |Mensa-Annan | 10| 7| 2| 3|1576562.1966|98055 | Lynn |Tsoflias | 11| 7| 2| 3|1421810.9242|98055 | Rachel |Valdez | 12| 7| 2| 4|1827066.7118|98055 | Jae |Pak | 13| 7| 2| 4|4116871.2277|98055 | Ranjit |Varkey Chudukatil| 14| 7| 2| 4|3121616.3202|98055 |
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: Order table conditionally using case statement.
Next: Skip a number of rows from a sorted table.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics