AdventureWorks Database: Order the result set by the column TerritoryName using CASE
124. From the following table write a query in SQL to order the result set by the column TerritoryName when the column CountryRegionName is equal to 'United States' and by CountryRegionName for all other rows. Return BusinessEntityID, LastName, TerritoryName, CountryRegionName.
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
SELECT
-- Selecting the BusinessEntityID column
BusinessEntityID,
-- Selecting the LastName column
LastName,
-- Selecting the TerritoryName column
TerritoryName,
-- Selecting the CountryRegionName column
CountryRegionName
-- Selecting data from the vSalesPerson view
FROM
Sales.vSalesPerson
-- Filtering records where TerritoryName is not NULL
WHERE
TerritoryName IS NOT NULL
-- Ordering the result set based on a conditional expression
ORDER BY
CASE
-- If the CountryRegionName is 'United States', order by TerritoryName
WHEN CountryRegionName = 'United States' THEN TerritoryName
-- If the CountryRegionName is not 'United States', order by CountryRegionName
ELSE CountryRegionName
END;
Explanation:
- This SQL code selects specific columns from the vSalesPerson view.
- It retrieves data related to salespersons' BusinessEntityID, LastName, TerritoryName, and CountryRegionName.
- Records are filtered to include only those where TerritoryName is not NULL.
- The ORDER BY clause sorts the result set based on a conditional expression.
- If the CountryRegionName is 'United States', the result set is ordered by TerritoryName.
- If the CountryRegionName is not 'United States', the result set is ordered by CountryRegionName.
- This ordering ensures that salespersons within the United States are ordered by TerritoryName, while salespersons from other countries are ordered by CountryRegionName.
Sample Output:
businessentityid|lastname |territoryname |countryregionname|
----------------+-----------------+--------------+-----------------+
286|Tsoflias |Australia |Australia |
282|Saraiva |Canada |Canada |
278|Vargas |Canada |Canada |
277|Carson |Central |United States |
290|Varkey Chudukatil|France |France |
288|Valdez |Germany |Germany |
275|Blythe |Northeast |United States |
280|Ansman-Wolfe |Northwest |United States |
284|Mensa-Annan |Northwest |United States |
283|Campbell |Northwest |United States |
279|Reiter |Southeast |United States |
276|Mitchell |Southwest |United States |
281|Ito |Southwest |United States |
289|Pak |United Kingdom|United Kingdom |
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: Return first and last name, and other columns using partition by clause.
Next: Return the highest hourly wage for each job title.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.