AdventureWorks Database: Return first and last name, and other columns using partition by clause
SQL Query - AdventureWorks: Exercise-123 with Solution
123. From the following table write a query in SQL to return first name, last name, territoryname, salesytd, and row number. Partition the query result set by the TerritoryName. Orders the rows in each partition by SalesYTD. Sort the result set on territoryname in ascending order.
Sample table: Sales.vSalesPerson
businessentityid|title|firstname|middlename|lastname |suffix|jobtitle |phonenumber |phonenumbertype|emailaddress |emailpromotion|addressline1 |addressline2|city |stateprovincename|postalcode|countryregionname|territoryname |territorygroup|salesquota|salesytd |saleslastyear|
----------------+-----+---------+----------+-----------------+------+----------------------------+-------------------+---------------+----------------------------+--------------+--------------------------+------------+-------------+-----------------+----------+-----------------+--------------+--------------+----------+------------+-------------+
274| |Stephen |Y |Jiang | |North American Sales Manager|238-555-0197 |Cell |[email protected]| 0|2427 Notre Dame Ave. | |Redmond |Washington |98052 |United States | | | | 559697.5639| 0|
275| |Michael |G |Blythe | |Sales Representative |257-555-0154 |Cell |[email protected]| 1|8154 Via Mexico | |Detroit |Michigan |48226 |United States |Northeast |North America | 300000|3763178.1787| 1750406.4785|
276| |Linda |C |Mitchell | |Sales Representative |883-555-0116 |Work |[email protected] | 0|2487 Riverside Drive | |Nevada |Utah |84407 |United States |Southwest |North America | 250000|4251368.5497| 1439156.0291|
277| |Jillian | |Carson | |Sales Representative |517-555-0117 |Work |[email protected]| 1|80 Sunview Terrace | |Duluth |Minnesota |55802 |United States |Central |North America | 250000|3189418.3662| 1997186.2037|
278| |Garrett |R |Vargas | |Sales Representative |922-555-0165 |Work |[email protected]| 0|10203 Acorn Avenue | |Calgary |Alberta |T2P 2G8 |Canada |Canada |North America | 250000|1453719.4653| 1620276.8966|
279| |Tsvi |Michael |Reiter | |Sales Representative |664-555-0112 |Work |[email protected] | 1|8291 Crossbow Way | |Memphis |Tennessee |38103 |United States |Southeast |North America | 300000| 2315185.611| 1849640.9418|
280| |Pamela |O |Ansman-Wolfe | |Sales Representative |340-555-0193 |Cell |[email protected] | 1|636 Vine Hill Way | |Portland |Oregon |97205 |United States |Northwest |North America | 250000|1352577.1325| 1927059.178|
281| |Shu |K |Ito | |Sales Representative |330-555-0120 |Cell |[email protected] | 2|5725 Glaze Drive | |San Francisco|California |94109 |United States |Southwest |North America | 250000|2458535.6169| 2073505.9999|
282| |José |Edvaldo |Saraiva | |Sales Representative |185-555-0169 |Work |josé[email protected] | 0|9100 Sheppard Avenue North| |Ottawa |Ontario |K4B 1T7 |Canada |Canada |North America | 250000|2604540.7172| 2038234.6549|
283| |David |R |Campbell | |Sales Representative |740-555-0182 |Work |[email protected] | 0|2284 Azalea Avenue | |Bellevue |Washington |98004 |United States |Northwest |North America | 250000|1573012.9383| 1371635.3158|
284|Mr. |Tete |A |Mensa-Annan | |Sales Representative |615-555-0153 |Work |[email protected] | 1|3997 Via De Luna | |Cambridge |Massachusetts |02139 |United States |Northwest |North America | 300000|1576562.1966| 0|
285|Mr. |Syed |E |Abbas | |Pacific Sales Manager |926-555-0182 |Work |[email protected] | 0|7484 Roundtree Drive | |Bothell |Washington |98011 |United States | | | | 172524.4512| 0|
286| |Lynn |N |Tsoflias | |Sales Representative |1 (11) 500 555-0190|Cell |[email protected] | 2|34 Waterloo Road | |Melbourne |Victoria |3000 |Australia |Australia |Pacific | 250000|1421810.9242| 2278548.9776|
287| |Amy |E |Alberts | |European Sales Manager |775-555-0164 |Work |[email protected] | 1|5009 Orange Street | |Renton |Washington |98055 |United States | | | | 519905.932| 0|
288| |Rachel |B |Valdez | |Sales Representative |1 (11) 500 555-0140|Cell |[email protected] | 0|Pascalstr 951 | |Berlin |Hamburg |14111 |Germany |Germany |Europe | 250000|1827066.7118| 1307949.7917|
289| |Jae |B |Pak | |Sales Representative |1 (11) 500 555-0145|Work |[email protected] | 0|Downshire Way | |Cambridge |England |BA5 3HX |United Kingdom |United Kingdom|Europe | 250000|4116871.2277| 1635823.3967|
290| |Ranjit |R |Varkey Chudukatil| |Sales Representative |1 (11) 500 555-0117|Cell |[email protected] | 0|94, rue Descartes | |Bordeaux |Gironde |33000 |France |France |Europe | 250000|3121616.3202| 2396539.7601|
Sample Solution:
-- Selecting specific columns from the vSalesPerson view and calculating row numbers within each TerritoryName partition based on SalesYTD
SELECT
-- Selecting the FirstName column
FirstName,
-- Selecting the LastName column
LastName,
-- Selecting the TerritoryName column
TerritoryName,
-- Rounding SalesYTD to 2 decimal places and aliasing it as SalesYTD
ROUND(SalesYTD, 2) AS SalesYTD,
-- Calculating row numbers within each TerritoryName partition based on SalesYTD in descending order
ROW_NUMBER() OVER(PARTITION BY TerritoryName ORDER BY SalesYTD DESC) AS Row
-- Selecting data from the vSalesPerson view
FROM
Sales.vSalesPerson
-- Filtering records where TerritoryName is not NULL and SalesYTD is not equal to 0
WHERE
TerritoryName IS NOT NULL
AND SalesYTD <> 0
-- Ordering the result set by TerritoryName
ORDER BY
TerritoryName;
Explanation:
- This SQL code selects specific columns from the vSalesPerson view and calculates row numbers within each TerritoryName partition based on SalesYTD.
- The ROW_NUMBER() function is used to calculate row numbers within each TerritoryName partition, ordering the rows by SalesYTD in descending order.
- The result set includes columns for FirstName, LastName, TerritoryName, SalesYTD (rounded to 2 decimal places), and the calculated row numbers (Row).
- Records are filtered to include only those where TerritoryName is not NULL and SalesYTD is not equal to 0.
- The result set is ordered by TerritoryName.
- Each row within a TerritoryName partition will have a unique row number based on SalesYTD, with the highest SalesYTD value receiving the row number of 1, the second-highest receiving 2, and so on.
Sample Output:
firstname|lastname |territoryname |salesytd |row| ---------+-----------------+--------------+----------+---+ Lynn |Tsoflias |Australia |1421810.92| 1| José |Saraiva |Canada |2604540.72| 1| Garrett |Vargas |Canada |1453719.47| 2| Jillian |Carson |Central |3189418.37| 1| Ranjit |Varkey Chudukatil|France |3121616.32| 1| Rachel |Valdez |Germany |1827066.71| 1| Michael |Blythe |Northeast |3763178.18| 1| Tete |Mensa-Annan |Northwest |1576562.20| 1| David |Campbell |Northwest |1573012.94| 2| Pamela |Ansman-Wolfe |Northwest |1352577.13| 3| Tsvi |Reiter |Southeast |2315185.61| 1| Linda |Mitchell |Southwest |4251368.55| 1| Shu |Ito |Southwest |2458535.62| 2| Jae |Pak |United Kingdom|4116871.23| 1|
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: Calculate row numbers for rows between 50 to 60 inclusive.
Next: Order the result set by the column TerritoryName using CASE.
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