Returning query results in predefined order

Question

Is it possible to do a SELECT statement with a predetermined order, ie. selecting IDs 7,2,5,9 and 8 and returning them in that order, based on nothing more than the ID field?

Statements SELECT id FROM table WHERE id in (7,2,5,9,8); and SELECT id FROM table WHERE id in (8,2,5,9,7); both return them in the same order.

Answer 1

All ordering is done by the ORDER BY keywords, you can only however sort ascending and descending. If you are using a language such as PHP you can then sort them accordingly using some code but I do not believe it is possible with MySQL alone.

Answer 2

This works in Oracle. Can you do something similar in MySql?

SELECT ID_FIELD
FROM SOME_TABLE
WHERE ID_FIELD IN(11,10,14,12,13)
ORDER BY
  CASE WHEN ID_FIELD = 11 THEN 0
       WHEN ID_FIELD = 10 THEN 1
       WHEN ID_FIELD = 14 THEN 2
       WHEN ID_FIELD = 12 THEN 3
       WHEN ID_FIELD = 13 THEN 4
  END

Answer 3

Could you include a case expression that maps your IDs 7,2,5,... to the ordinals 1,2,3,... and then order by that expression?

Answer 4

I didn't think this was possible, but found a blog entry here that seems to do the type of thing you're after:

SELECT id FROM table WHERE id in (7,2,5,9,8) 
ORDER BY FIND_IN_SET(id,"7,2,5,9,8");

will give different results to

SELECT id FROM table WHERE id in (7,2,5,9,8) 
ORDER BY FIND_IN_SET(id,"8,2,5,9,7");

FIND_IN_SET returns the position of id in the second argument given to it, so for the first case above, id of 7 is at position 1 in the set, 2 at 2 and so on - mysql internally works out something like

id | FIND_IN_SET
---|-----------
7  | 1
2  | 2
5  | 3

then orders by the results of FIND_IN_SET.

Answer 5

You may need to create a temp table with an autonumber field and insert into it in the desired order. Then sort on the new autonumber field.

Answer 6

It's hacky (and probably slow), but you can get the effect with UNION ALL:

SELECT id FROM table WHERE id = 7
UNION ALL SELECT id FROM table WHERE id = 2
UNION ALL SELECT id FROM table WHERE id = 5
UNION ALL SELECT id FROM table WHERE id = 9
UNION ALL SELECT id FROM table WHERE id = 8;

Edit: Other people mentioned the find_in_set function which is documented here.

Answer 7

Best I can think of is adding a second Column orderColumn:

7 1
2 2
5 3
9 4 
8 5

And then just do a ORDER BY orderColumn

Answer 8

Erm, not really. Closest you can get is probably:

SELECT * FROM table WHERE id IN (3, 2, 1, 4) ORDER BY id=4, id=1, id=2, id=3

But you probably don't want that :)

It's hard to give you any more specific advice without more information about what's in the tables.

Answer 9

ORDER BY FIELD(ID,7,2,4,5,8) is your best bet, but it's still ugly.

Answer 10

You get answers fast around here, don't you…

The reason I'm asking this is that it's the only way I can think of to avoid sorting a complex multidimensional array. I'm not saying it would be difficult to sort, but if there were a simpler way to do it with straight sql, then why not.

Answer 11

One Oracle solution is:

SELECT id FROM table WHERE id in (7,2,5,9,8)
ORDER BY DECODE(id,7,1,2,2,5,3,9,4,8,5,6);

This assigns an order number to each ID. Works OK for a small set of values.