w3resource

AdventureWorks Database: Order the result set by the column TerritoryName using CASE

SQL Query - AdventureWorks: Exercise-124 with Solution

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|

Click to view Full table

Sample Solution:

SELECT BusinessEntityID, LastName, TerritoryName, CountryRegionName  
FROM Sales.vSalesPerson  
WHERE TerritoryName IS NOT NULL  
ORDER BY CASE CountryRegionName WHEN 'United States' THEN TerritoryName  
         ELSE CountryRegionName END;

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.



Follow us on Facebook and Twitter for latest update.