CODEWITHSUNDEEP
sql-servert-sql
Jacob
Jacob

Reputation: 346

ORDER BY not working when using CASE WHEN, but works without it?

I have the following select statement where I am trying to order my results in a specific way, specifically the hierarchy of the department. However when I add cases, my ORDER BY stops working and gives me an error:

ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

I don't know why this is happening because I am selecting all in each SELECT statement.

Select * FROM AccountAdminTable Where CollegeCode = 'A10' AND AdminLevel LIKE 'Account Administrator'
Union
Select * FROM AccountAdminTable Where CollegeCode = 'A10' AND AdminLevel LIKE 'Department Head/Director'
Union
Select * FROM AccountAdminTable Where CollegeCode = 'A10' AND AdminLevel LIKE 'Dean/Vice President'
Order By AdminLevel ASC

This doesn't work:

Select * FROM AccountAdminTable Where CollegeCode = 'A' AND AdminLevel LIKE 'Account Administrator'
Union
Select * FROM AccountAdminTable Where CollegeCode = 'A' AND AdminLevel LIKE 'Department Head/Director'
Union
Select * FROM AccountAdminTable Where CollegeCode = 'A' AND AdminLevel LIKE 'Dean/Vice President'
Order By Case
    When AdminLevel = 'Account Administrator' Then 1
    When AdminLevel = 'Department Head/Director' Then 2
    When AdminLevel = 'Dean/Vice President' Then 3
    Else 4 End ASC

Any help is greatly appreciated! Below is some sample data.

College Code| AdminLevel               | Name
A           | Account Administrator    | Bob
A           | Department Head/Director | Tim
A           | Dean/Vice President      | Jeff
B           | Account Administrator    | Gary
B           | Department Head/Director | Phil
B           | Dean/Vice President      | John
C           | Account Administrator    | Bill
C           | Account Administrator    | Larry

Upvotes: 0

Views: 239

Answers (3)

Salman Arshad
Salman Arshad

Reputation: 272106

Another option to write your query is this:

SELECT AccountAdminTable.*
FROM AccountAdminTable
INNER JOIN (VALUES
    (1, 'Account Administrator'),
    (2, 'Dept HD'),
    (3, 'Dean/VP')
) AS vlist(Sort, AdminLevel) ON AccountAdminTable.AdminLevel = vlist.AdminLevel
WHERE CollegeCode = 'A'
ORDER BY vlist.Sort

Or better, create a table for admin levels and reference it via foreign key.

Upvotes: 2

Tab Alleman
Tab Alleman

Reputation: 31785

You know, you really don't need to use a UNION at all here. Since all your SELECTS are from the same table, you could just do this (note, abbreviations used in my pseudo code below. use correct values in your code, obviously):

Select * FROM AccountAdminTable 
Where CollegeCode = 'A'
AND AdminLevel IN ('Account Administrator','Dept HD','Dean/VP')
ORDER BY CASE {Your CASE Expression}

Upvotes: 6

Luis Cazares
Luis Cazares

Reputation: 3585

You have 2 options:

  • Use a derived table to use the CASE in the ORDER BY.
SELECT *
FROM(
         Select * FROM AccountAdminTable Where CollegeCode = 'A' AND AdminLevel LIKE 'Account Administrator'
        Union
        Select * FROM AccountAdminTable Where CollegeCode = 'A' AND AdminLevel LIKE 'Department Head/Director'
        Union
        Select * FROM AccountAdminTable Where CollegeCode = 'A' AND AdminLevel LIKE 'Dean/Vice President'
        )t
Order By Case 
        When AdminLevel = 'Account Administrator' Then 1
        When AdminLevel = 'Department Head/Director' Then 2
        When AdminLevel = 'Dean/Vice President' Then 3
        Else 4 End ASC;

*Or add a column that defines the order.

Select *, 1 AS OrderCol FROM AccountAdminTable Where CollegeCode = 'A' AND AdminLevel LIKE 'Account Administrator'
Union
Select *, 2 AS OrderCol FROM AccountAdminTable Where CollegeCode = 'A' AND AdminLevel LIKE 'Department Head/Director'
Union
Select *, 3 AS OrderCol FROM AccountAdminTable Where CollegeCode = 'A' AND AdminLevel LIKE 'Dean/Vice President'
Order By OrderCol;

Either way, you might want to change the UNION into UNION ALL to improve performance. Unless you're actually eliminating duplicates.

Upvotes: 4

Related Questions