Query with no autosort [duplicate]

This question already has answers here: Closed 12 years ago.

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
开发者_如何学Python

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

---

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)

---

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

---

If the sort order is not dynamic but is intrinsic to the product, you probably need another field in your table for SortOrder that you can use in the ORDER BY clause:

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

Where SortOrder looks like this:

Id  SortOrder
1   20 
2   40
3   10
4   30