ORDER BY ... IN(...) ASC

Question

I want to get all profiles first that are for certain location(s):

SELECT * FROM profile
WHERE location IN ('a', 'b', 'c') OR isDefault=1 
ORDER BY location IN ('a', 'b') DESC, -- put to the front if location in 'a','b'
         isDefault DESC,              -- for each of both groups, put default profiles before the others.
         location ASC                 -- and sort each of the up to four groups by location.

This throws the error: "Incorrect syntax near the keyword 'IN'.". If I remove the order clause, results are returned.

What is wrong here?

Answer 1

You could rewrite that to give an integer back which is sortable:

case when location IN ('a', 'b') then 0 else 1 end DESC

Answer 2

You can't use IN in the ORDER BY. Change it to:

ORDER BY location ASC, --will sort a, then b, then c