I have the following mysql query:
SELECT
col1,
col2,
col3
FROM
guests
WHERE
active = 1 AND
guestId IN (233, 255, 254, 226, 182, 231, 113, 121)
When I get the results however, I get them ordered by guestId (which is indexed) in ascending order (starting with 121 and ending with 255). I need to show the results on my website in that specific order. Is there a way I can bypass the default order?
Thanks.
You have to specify the custom order in your ORDER BY clause:
SELECT col1, col2, col3 FROM guests
WHERE active = 1 AND guestId IN (233, 255, 254, 226, 182, 231, 113, 121)
ORDER BY FIND_IN_SET(guestId, '233,255,254,226,182,231,113,121');
The builtin FIND_IN_SET() function returns an integer which is the position that the first argument finds its match in the list. Note the list is a single string argument containin a comma-separated list, whereas your IN() predicate needs a variable number of arguments. Also IIRC there must be no spaces in the list you give to FIND_IN_SET().
Append ORDER BY guestId DESC to the query so it looks like this:
SELECT
col1,
col2,
col3
FROM
guests
WHERE
active = 1 AND
guestId IN (233, 255, 254, 226, 182, 231, 113, 121)
ORDER BY
guestId DESC
