CODEWITHSUNDEEP
sqlsql-order-bycase
Haluk Karamete
Haluk Karamete

Reputation: 33

preserving the order in SQL query

I have a SQL query like this:

SELECT * from Ask where Number IN 1,3,2

This returns the results as 1,2,3 as opposed to 1,3,2.

Is there a way to force the matching recordset to preserve the query order?

Upvotes: 0

Views: 155

Answers (1)

GMB
GMB

Reputation: 222382

You can use a case expression in the order by clause:

select * 
from ask 
where num in (1,3,2)
order by case num
    when 1 then 1
    when 3 then 2
    when 2 then 3
end

Depending on your database, syntactical shortcuts may be available.

Oracle has decode():

order by decode(num, 1, 1, 3, 2, 2, 3)

MySQL has field():

order by field(num, 1, 3, 2)

Side note: num is a language keyword in many databases. I renamed it to num in the query.

Upvotes: 2

Related Questions