Query with no autosort [duplicate]

Question

Possible Duplicates:
Ordering MySQL results by IN sequence?
Ordering by the order of values in a SQL IN() clause

i have the following table called "Products"

id desc
1  BL10
2  BL15
3  BL45
4  BL50

well, this is my query SELECT * FROM Products WHERE id IN(3,1,4,2) I want it shows in the same order of clause IN, i.e. 3,1,4,2 :

id desc
3  BL45
1  BL10
4  BL50
2  BL15

but when i execute it, it shows ordered, How can i get that? I'm using MSSQL 2005

Answer 1

Your IN clause won't sort your result set, but instead your results will be returned in the order they are encountered in the query.

If you wanted, you could create an explicit ORDER BY to sort it in the order you want:

SELECT * 
FROM Products
WHERE id IN (3,1,4,2)
ORDER BY (CASE WHEN id = 3 THEN 0
               WHEN id = 1 THEN 1
               WHEN id = 4 THEN 2
               WHEN id = 2 THEN 3 END)

(This is DBMS-nonspecific, with the caveat that it'd be a pain in the ass to write if you have more values that you want in a specific order)

Answer 2

SELECT * FROM Products WHERE id IN (3, 1, 4, 2)
ORDER BY FIELD (id, 3, 1, 4, 2)