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


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.



Share this Tutorial / Exercise on : Facebook and Twitter

SQL: Tips of the Day

Convert Timestamp to date in MySQL Query:

DATE_FORMAT(FROM_UNIXTIME(`user.registration`), '%e %b %Y') AS 'date_formatted'

Database: MySQL

Ref : https://bit.ly/3EJPnMQ