CODEWITHSUNDEEP
sqlsql-serversql-order-bydistinct
JB Hurteaux
JB Hurteaux

Reputation: 4538

SELECT DISTINCT with ORDER BY and CASE WHEN

My query is:

SELECT DISTINCT LanguageCode
       FROM myTable
       ORDER BY (CASE WHEN LanguageCode='GB' THEN 0 ELSE 1 END), LanguageCode

The goal is to get languages, but to get GB language first. And I'm getting this error:

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

I have seen the answers to this question: Reason for - ORDER BY items must appear in the select list if SELECT DISTINCT is specified

But I still don't understand why it applies to me (and how to solve it) as I'm only accessing a single column.

Upvotes: 0

Views: 469

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Use aggregation instead:

SELECT LanguageCode
FROM myTable
GROUP BY LanguageCode
ORDER BY (CASE WHEN LanguageCode = 'GB' THEN 0 ELSE 1 END), 
         LanguageCode

Some databases are finicky about non-aggregated expressions in the ORDER BY. If that is the case here, then you can use:

SELECT LanguageCode
FROM myTable
GROUP BY LanguageCode
ORDER BY MAX(CASE WHEN LanguageCode = 'GB' THEN 0 ELSE 1 END), 
         LanguageCode

Upvotes: 0

JB Hurteaux
JB Hurteaux

Reputation: 4538

Somehow a workaround solution found by a colleague:

SELECT DISTINCT (CASE WHEN LanguageCode='GB' THEN 0 ELSE 1 END) AS IsGB, LanguageCode
   FROM myTable
   ORDER BY IsGB ASC, LanguageCode

Upvotes: 1

Related Questions