AdventureWorks Database: Order a table in ASC or DESC condionally

SQL Query - AdventureWorks: Exercise-32 with Solution

32. From the following table write a query in SQL to ordered the BusinessEntityID column descendingly when SalariedFlag set to 'true' and BusinessEntityID in ascending order when SalariedFlag set to 'false'. Return BusinessEntityID, SalariedFlag columns.

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 --

Click to view Full table

Sample Solution:

-- Selecting specific columns from the Employee table
SELECT BusinessEntityID, SalariedFlag  

-- From the HumanResources schema's Employee table
FROM HumanResources.Employee  

-- Ordering the results using a conditional CASE statement
-- If SalariedFlag is 'true', order by BusinessEntityID in descending order
-- If SalariedFlag is 'false', order by BusinessEntityID in ascending order
ORDER BY CASE SalariedFlag WHEN 'true' THEN BusinessEntityID END DESC  
        ,CASE WHEN SalariedFlag ='false' THEN BusinessEntityID END;

Explanation:

• The SQL query retrieves data from the Employee table within the HumanResources schema.
• It selects two columns: BusinessEntityID and SalariedFlag.
• The ORDER BY clause specifies the sorting criteria for the result set using conditional expressions.
• The first CASE statement sorts the rows where SalariedFlag is 'true' by BusinessEntityID in descending order.
• The second CASE statement sorts the rows where SalariedFlag is 'false' by BusinessEntityID in ascending order.
• This results in a mixed sorting order where rows with SalariedFlag set to 'true' are sorted first in descending order by BusinessEntityID, followed by rows with SalariedFlag set to 'false' sorted in ascending order by BusinessEntityID.

Sample Output:

businessentityid|salariedflag|
----------------+------------+
               4|false       |
              11|false       |
              12|false       |
              13|false       |
              17|false       |
              18|false       |
              19|false       |
              20|false       |
              21|false       |
              22|false       |
              23|false       |
              24|false       |
              27|false       |
              28|false       |
              29|false       |
              30|false       |
              31|false       |
...			  

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Retrieve persons last name begins with R.
Next: Order table conditionally using case statement.