ORDER BY Has Error on Run Query With Case When

user_10525175 0
Viewed 50

I have this query:

SELECT DISTINCT *
FROM 
    (SELECT h.ID, h.Title 
     FROM table1 h
     LEFT JOIN table2 k ON h.ID = k.MediaID) a
ORDER BY 
    CASE 
       WHEN 1 = 1 THEN a.Title 
       ELSE a.ID
    END ASC

And my tables are like this:

table1 

    id | title
    ---+------
     1 | aaaaa
     2 | bbbb
     3 | ccc
     4 | ddddd

table2 

    id | mediaId
    ---+---------
     1 |    2
     2 |    2
     3 |    4
     4 |    1

When I run this query, I get this error:

ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

What's wrong whit this code?

If I remove Else it works fine

sql
1 Answers

A case expression returns a single value, with a single type. You are mixing types and hence getting an error. The rules of SQL says that strings are converted to numbers in this situation -- and that is where the error is happening.

The simple solution is two expressions:

1 = 1 is rather arcane. This would normally be something like:

order by (case when @var = 1 then a.title end),
         (case when @var = 2 then null else a.id end)

You could get rid of the error with an explicit conversion:

order by (case when 1 = 1 then a.title end),
               else cast(a.id as varchar(255)
          end)

However, this would not sort the data numerically for the second case.

user_11440350
Related