53

I am going to have a fixed list of items to order by that I won't know until I run the query since there is a randomization step.

I would like to have something like the following:

Assume that is_launch_set will return 1, 3, 7, 11 but have been randomized to below:

SELECT * FROM items WHERE is_launch_set=1 ORDER BY id values (3,11,7,1);

Any ideas on how to achieve this? I was thinking maybe a find_in_set but not really sure.

Giacomo1968
  • 25,759
  • 11
  • 71
  • 103
timpone
  • 19,235
  • 36
  • 121
  • 211
  • Possible Duplicate: [MySQL Sort by some list](http://stackoverflow.com/questions/6810793/), although this one attracted a more thorough answer. – Patrick M Sep 29 '14 at 14:44

2 Answers2

107

You can do that by using either:

ORDER BY FIND_IN_SET(id, '3,11,7,1')

or

ORDER BY FIELD(id, 3, 11, 7, 1)

or

ORDER BY CASE id WHEN 3 THEN 0
                WHEN 11 THEN 1
                 WHEN 7 THEN 2
                 WHEN 1 THEN 3
                        ELSE 4
         END
zerkms
  • 249,484
  • 69
  • 436
  • 539
  • 1
    are you sure the find in set would work - before I asked the quesiton, that's what I thought the answer would be but the ordering seems different each time. Are there any config settings or anything else. The field answer looks good. – timpone Jun 07 '12 at 22:07
  • @timpone: I'm sure it works. You could put `FIND_IN_SET(id, '3,11,7,1')` to the `SELECT` to make sure it returns what expected – zerkms Jun 07 '12 at 22:10
  • 1
    Do you have any information on which of these approaches is the fastest performing? Is there anything one can do to speed it up besides putting an index on the `id` field? – Patrick M Sep 29 '14 at 14:27
  • @Patrick M: you could try it first. Performance optimization requires personal checks – zerkms Sep 29 '14 at 19:43
  • That last one is really useful for clarity of the code, thanks. – M1ke Nov 27 '14 at 12:20
  • 11
    Just tested each of the first two methods using 10k records from a 250k record set. FIND_IN_SET took about 4.5 seconds, FIELD 500ms. – Synexis Mar 09 '15 at 16:45
  • @Synexis: they perform similar operation with full scan. So it does not matter if there is minor difference between them. – zerkms Mar 09 '15 at 20:25
  • For anyone interested [here](https://dba.stackexchange.com/a/109126/57479) [@rolandomysqldba](https://dba.stackexchange.com/users/877/rolandomysqldba) shared some details about how this works – fgilio Jun 21 '19 at 18:04
0

In mys sql 8.0 it order only by the first column - 3 in this case:

ORDER BY FIND_IN_SET(id, '3,11,7,1')

With this example it worked, but upside. you'll see the 3 last, then 11, then 7 etc.

ORDER BY FIELD(id, 3, 11, 7, 1)

If you chose this example, you'll see the 1 first, then 7, then 11 etc.

ORDER BY FIELD(id, 3, 11, 7, 1) DESC
Israel
  • 1,165
  • 11
  • 11