Posted by Andy Hassall on 11/10/06 16:58

On Sun, 5 Nov 2006 11:12:29 -0000, "Yobbo" <info@SpamMeNot.co.uk> wrote:

>My query is as follows:
>
>SELECT STRINGTEXT, TOKENID
>FROM WEBSTRINGS
>WHERE TOKENID IN (6,20,234,19,32,4,800,177)
>
>All I want is my resultset to come back in the order that I have defined in
>the IN clause, but unfortunately SQL is trying to be too helpful and sorts
>the numbers in the IN clause so that the resultset comes back with a TOKENID
>order of 4,6,19,20,32,177,234,800.

SQL gives no guarantee of ordering without an ORDER BY clause. It's not
sorting the IN clause as such, it's more likely that the most reasonable way to
get the data out for your request uses an index, and so happens to come out
ordered the same as in the index - but this still isn't guaranteed in any way.

To get an ordering, add an ORDER BY clause. You can get an arbitrary ordering
with CASE, for example:

mysql> select c
-> from t
-> where c in (6,20,234,19,32,4,800,177)
-> order by case c
-> when 6 then 0
-> when 20 then 1
-> when 234 then 2
-> when 19 then 3
-> when 32 then 4
-> when 4 then 5
-> when 800 then 6
-> when 177 then 7
-> end;
+------+
| c |
+------+
| 6 |
| 20 |
| 234 |
| 19 |
| 32 |
| 4 |
| 800 |
| 177 |
+------+
8 rows in set (0.00 sec)

--
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

[Back to original message]