How to ORDER BY designated list of key values

[pr0test0r]

I need to be able to order the results of a SELECT query by the order of a specific list of key IDs provided in the WHERE IN statement.

So my query looks like:

SELECT *
FROM TableName
WHERE KeyID IN (3,104,43,22,345)
ORDER BY ????

I need the results returned in the order provided in the IN list (3,104,43,22,345).

Thanks in advance!

pr0

[rmiao]

You can only order by column, not values of the column. Try use union to put them together in your designated order.

[pr0test0r]

I'm not quite sure how to use UNION for that purpose. Could you elaborate? Thanks!

[russellb]

SELECT *
FROM TableName
WHERE KeyID IN (3,104,43,22,345)
ORDER BY
	Case KeyID
		WHEN 3 Then 1
		WHEN 104 Then 2
		WHEN 43 Then 3
		WHEN 22 Then 4
		WHEN 345 Then 5
	End

[pr0test0r]

Thanks russellb! That was the perfect solution. I never would have figured that one out.

pr0

[russellb]

glad 2 help. the other way, which i think rm was going after is this

select *, 1 as sortVal from table where KeyID = 3
UNION
select *, 2 as sortVal from table where KeyID = 104
UNION
select *, 3 as sortVal from table where KeyID = 43
...
ORDER BY sortVal