当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - 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?

网友答案:

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

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

网友答案:

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
分享给朋友:
您可能感兴趣的文章:
随机阅读: