Getting back set order from the IN param

Hi All

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.

I don't want this bloody order I want 6,20,234,19,32,4,800,177!!

Sorry for my rant, but its got my hot under the collar.

Is there anyway round this?

Thanks

Yobbo

Re: Getting back set order from the IN param

On Sun, 5 Nov 2006 11:12:29 -0000, "Yobbo" 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

Re: Getting back set order from the IN param

Yobbo wrote:

> I don't want this bloody order I want 6,20,234,19,32,4,800,177!!
>

Funny --- I came here right now to ask exactly that question! Although I
didn't find a answer, I somehow got inspired. Strange ...

Here is the solution:

SELECT STRINGTEXT, TOKENID
FROM WEBSTRINGS
WHERE TOKENID IN (6,20,234,19,32,4,800,177)
ORDER BY FIELD(TOKENID, 6,20,234,19,32,4,800,177)

You have to repeat the set but that's easily done in PHP.

Best regards,
Martin