AdventureWorks Database: Sort the BusinessEntityID in descending or ascending order

SQL Query - AdventureWorks: Exercise-126 with Solution

126. From the following table write a query in SQL to sort the BusinessEntityID in descending order for those employees that have the SalariedFlag set to 'true' and in ascending order that have the SalariedFlag set to 'false'. Return BusinessEntityID, and SalariedFlag.

businessentityid|nationalidnumber|loginid                     |jobtitle                                |birthdate |maritalstatus|gender|hiredate  |salariedflag|vacationhours|sickleavehours|currentflag|rowguid                             |modifieddate           |organizationnode|
----------------+----------------+----------------------------+----------------------------------------+----------+-------------+------+----------+------------+-------------+--------------+-----------+------------------------------------+-----------------------+----------------+
               1|295847284       |adventure-works\ken0        |Chief Executive Officer                 |1969-01-29|S            |M     |2009-01-14|true        |           99|            69|true       |f01251e5-96a3-448d-981e-0f99d789110d|2014-06-30 00:00:00.000|/               |
               2|245797967       |adventure-works\terri0      |Vice President of Engineering           |1971-08-01|S            |F     |2008-01-31|true        |            1|            20|true       |45e8f437-670d-4409-93cb-f9424a40d6ee|2014-06-30 00:00:00.000|/1/             |
               3|509647174       |adventure-works\roberto0    |Engineering Manager                     |1974-11-12|M            |M     |2007-11-11|true        |            2|            21|true       |9bbbfb2c-efbb-4217-9ab7-f97689328841|2014-06-30 00:00:00.000|/1/1/           |
               4|112457891       |adventure-works\rob0        |Senior Tool Designer                    |1974-12-23|S            |M     |2007-12-05|false       |           48|            80|true       |59747955-87b8-443f-8ed4-f8ad3afdf3a9|2014-06-30 00:00:00.000|/1/1/1/         |
               5|695256908       |adventure-works\gail0       |Design Engineer                         |1952-09-27|M            |F     |2008-01-06|true        |            5|            22|true       |ec84ae09-f9b8-4a15-b4a9-6ccbab919b08|2014-06-30 00:00:00.000|/1/1/2/         |
               6|998320692       |adventure-works\jossef0     |Design Engineer                         |1959-03-11|M            |M     |2008-01-24|true        |            6|            23|true       |e39056f1-9cd5-478d-8945-14aca7fbdcdd|2014-06-30 00:00:00.000|/1/1/3/         |
               7|134969118       |adventure-works\dylan0      |Research and Development Manager        |1987-02-24|M            |M     |2009-02-08|true        |           61|            50|true       |4f46deca-ef01-41fd-9829-0adab368e431|2014-06-30 00:00:00.000|/1/1/4/         |
               8|811994146       |adventure-works\diane1      |Research and Development Engineer       |1986-06-05|S            |F     |2008-12-29|true        |           62|            51|true       |31112635-663b-4018-b4a2-a685c0bf48a4|2014-06-30 00:00:00.000|/1/1/4/1/       |
               9|658797903       |adventure-works\gigi0       |Research and Development Engineer       |1979-01-21|M            |F     |2009-01-16|true        |           63|            51|true       |50b6cdc6-7570-47ef-9570-48a64b5f2ecf|2014-06-30 00:00:00.000|/1/1/4/2/       |
              10|879342154       |adventure-works\michael6    |Research and Development Manager        |1984-11-30|M            |M     |2009-05-03|true        |           16|            64|true       |eaa43680-5571-40cb-ab1a-3bf68f04459e|2014-06-30 00:00:00.000|/1/1/4/3/       |
			  -- more --

Click to view Full table

Sample Solution:

-- Selecting the BusinessEntityID and SalariedFlag columns from the Employee table
SELECT 
    BusinessEntityID, 
    SalariedFlag  
-- Selecting data from the Employee table
FROM 
    HumanResources.Employee  
-- Ordering the result set using a compound ORDER BY clause with two CASE expressions
ORDER BY 
    -- Ordering rows where SalariedFlag is 'true' in descending order based on BusinessEntityID
    CASE 
        WHEN SalariedFlag = 'true' THEN BusinessEntityID 
    END DESC,  
    -- Ordering rows where SalariedFlag is 'false' in ascending order based on BusinessEntityID
    CASE 
        WHEN SalariedFlag = 'false' THEN BusinessEntityID 
    END;

Explanation:

• This SQL code retrieves data related to BusinessEntityID and SalariedFlag from the Employee table.
• The ORDER BY clause arranges the result set using a compound ORDER BY clause with two CASE expressions.
• The first CASE expression orders rows where SalariedFlag is 'true' in descending order based on BusinessEntityID.
• The second CASE expression orders rows where SalariedFlag is 'false' in ascending order based on BusinessEntityID.
• This ordering arrangement ensures that rows where SalariedFlag is 'true' come first in descending order of BusinessEntityID, followed by rows where SalariedFlag is 'false' in ascending order of 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       |
...			  

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Return the highest hourly wage for each job title.
Next: Display the list price as a comment based on the price range.