CODEWITHSUNDEEP
sqlsql-server
Jaime Dolor jr.
Jaime Dolor jr.

Reputation: 929

How do I write my SQL Order By to follow the arrangement in the IN parameters

I need to have the ORDER BY in my SQL follow the arrangement based on the WHERE IN parameters. Here's my current SQL

SELECT a.code, a.name 
FROM sometable a 
WHERE a.code IN ('D001sa', 'EX!5', 'A$34')

The result I need to get is:

a.code      a.name
--------------------
'D001sa'    Home
'EX!5'      Family
'A$34'      Green

If I have either ORDER BY a.code or a.name, I don't get the results I need.

Upvotes: 1

Views: 43

Answers (2)

Ross Bush
Ross Bush

Reputation: 15185

You can use values as stated earlier or INNER JOIN a compound UNION query.

SELECT a.code, a.name FROM sometable st
INNER JOIN 
(
   SELECT code = 'D001sa', order = 1
   UNION
   SELECT code = 'EX!5', order = 2
   UNION
   SELECT code = 'A$34', order = 3
)AS X ON x.code=st.code
ORDER BY
    X.order

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270713

I would recommend using values():

SELECT a.code, a.name 
FROM sometable a JOIN
     (VALUES ('D001sa', 1), ('EX!5', 2), ('A$34', 3)
     ) v(code, ord)
     ON a.code = v.code
ORDER BY ord;

Upvotes: 4

Related Questions