CODEWITHSUNDEEP
sqlsql-server
Alexander
Alexander

Reputation: 20234

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

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?

Upvotes: 5

Views: 376

Answers (4)

AeroX
AeroX

Reputation: 3443

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

Upvotes: 1

Dasman
Dasman

Reputation: 317

So the ORDER BY Syntax is:

ORDER BY column_name ASC|DESC, column_name ASC|DESC

So

location IN ('a', 'b')

isDefault

location

have to be Column Names

Try:

location DESC
isDefault DESC

Also IN can only be used in a WHERE Clause

Sources:

http://www.w3schools.com/sql/sql_in.asp

http://www.w3schools.com/sql/sql_orderby.asp

Upvotes: 1

Patrick Hofman
Patrick Hofman

Reputation: 157098

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

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

Upvotes: 11

TTeeple
TTeeple

Reputation: 2989

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

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

Upvotes: 2

Related Questions