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?
You could rewrite that to give an integer back which is sortable:
case when location IN ('a', 'b') then 0 else 1 end DESC
You can't use
IN in the
ORDER BY. Change it to:
ORDER BY location ASC, --will sort a, then b, then c
So the ORDER BY Syntax is:
ORDER BY column_name ASC|DESC, column_name ASC|DESC
location IN ('a', 'b')
have to be Column Names
location DESC isDefault DESC
Also IN can only be used in a WHERE Clause
If you are using SQL Server 2012 or higher then you can use the
IIF() command to convert a true/false expression into a result using minimal code.
ORDER BY IIF(location IN ('a','b'), 1, 0) ASC