ORDER BY - order by the order listed WHERE col IN ()
I need to order a recordset by the same order that I have them in the conditional clause of the query.
SELECT ID, Name FROM MyTable WHERE ID IN (5,3,6)
I need to have the results ordered in the same order as shown in the query - in this example that would be 5,3,6.
SELECT ID, Name FROM MyTable WHERE ID IN (5,3,6)
I need to have the results ordered in the same order as shown in the query - in this example that would be 5,3,6.
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Or for a lack of a join table.
SELECT
CASE ID
WHEN 5 THEN 1
WHEN 3 THEN 2
WHEN 6 THEN 3
END AS Ordering,
ID,
Name
FROM
MyTable
WHERE
ID IN (5,3,6)
ORDER BY
Ordering
What this will do is use custom sorting on your IN values. In this case, you will only get those 3 ids, but I put in else to show how you would handle others. By setting to 4 and then ordering by ID you will get your IN statement in order then others will sort by ID normally.
SELECT ID, Name
FROM MyTable
WHERE ID IN (5,3,6)
ORDER BY Case ID When 5 Then 1 When 3 Then 2 When 6 Then 3 Else 4 End, ID
What if you have 10,000 IDs? That is a big ORDER BY or CASE statement statement....if you have a lookup table, you update the ranking once, and your query is simple to write.
Yes, but that would be too easy!
I was already typing and got hung up with something else. After posted, I saw all the previous comments. My solution was only meant for very limited case with static IN clause. If this is going to be done all the time with variable inputs, lookup would make a lot more sense.
I was already typing and got hung up with something else. After posted, I saw all the previous comments. My solution was only meant for very limited case with static IN clause. If this is going to be done all the time with variable inputs, lookup would make a lot more sense.
ASKER
Thanks for all the input. angelIII's solution works perfect and as it was first posted, and no one objects, I will go with that even though I am sure others will work as well.
SELECT t1.articleid, t1.headline, t1.summary, t2.name
FROM xlaANMarticles t1
LEFT OUTER JOIN xlaANMpublishers t2 ON t1.publisherid = t2.publisherid
JOIN ( SELECT 654 articleid, 1 order_value UNION ALL select 651, 2 UNION ALL select 659,3 ) l
ON t1.articleid = l.articleid
ORDER BY l.order_value
SELECT t1.articleid, t1.headline, t1.summary, t2.name
FROM xlaANMarticles t1
LEFT OUTER JOIN xlaANMpublishers t2 ON t1.publisherid = t2.publisherid
JOIN ( SELECT 654 articleid, 1 order_value UNION ALL select 651, 2 UNION ALL select 659,3 ) l
ON t1.articleid = l.articleid
ORDER BY l.order_value
SELECT ID, Name FROM MyTable m join lookup l on m.id = l.id WHERE ID IN (5,3,6)
order by l.ranking