3

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

Community
  • 1
  • 1
GuzZpaWn
  • 117
  • 2
  • 3
  • 6
  • 1
    depends whether it's mysql related – Johnno Nolan Aug 25 '10 at 20:23
  • 1
    SQL Server isn't actually covered well by the duplicate questions. [this trick](http://stackoverflow.com/questions/396748/ordering-by-the-order-of-values-in-a-sql-in-clause/396840#396840) should work. Or you could use a split table valued function to split the list and use that both for your `in` and ordering. – Martin Smith Aug 25 '10 at 20:33

3 Answers3

4

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)

Daniel Vandersluis
  • 91,582
  • 23
  • 169
  • 153
1
SELECT * FROM Products WHERE id IN (3, 1, 4, 2)
ORDER BY FIELD (id, 3, 1, 4, 2)
Eton B.
  • 6,121
  • 5
  • 31
  • 43
0

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
JeffSahol
  • 971
  • 8
  • 19