12

Is it possible to specify an arbitrary order for a MySQL SELECT statement? E.g.,

SELECT * FROM table_name WHERE id IN (1, 3, 2, 9, 7) ORDER BY (1, 3, 2, 9, 7);

The order of the numbers listed directly after IN do not seem to matter.

ma11hew28
  • 121,420
  • 116
  • 450
  • 651
  • @MattDiPasquale - You already has order in `1,3,2,9,7`, just load the results for `1,2,3,9,7`, and set it based on the order of `1,3,2,9,7`. Any specify programming language ? – ajreal Dec 01 '10 at 17:36
  • I'm using WordPress, PHP. I would like to do it all in MySQL so that I don't have to do any processing in PHP. – ma11hew28 Dec 01 '10 at 18:03
  • @MattDiPasquale - `find_in_set` is not fast and not very optimized. Do it in PHP is faster and optimized. – ajreal Dec 01 '10 at 18:08
  • @ajreal find_in_set is not slow unless the list is very large, mysql only needs to sort the 5 rows that it picked up from the Where – The Scrum Meister Dec 01 '10 at 18:43
  • @The Scrum Meister - 5 rows won't grow, but number of blog post will grow... – ajreal Dec 01 '10 at 18:50
  • When you do the order by a set of numbers like that it will order the result set based on field1, field3, field2, field9 and field7 of the fields in your SELECT *. It's a shorthand way of ordering the columns without using the fieldnames. – Leslie Dec 01 '10 at 20:23

3 Answers3

18

FIND_IN_SET function will do the trick

SELECT * FROM table_name WHERE id IN (1, 3, 2, 9, 7) ORDER BY FIND_IN_SET(id, '1,3,2,9,7');

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set

EDIT: Note the lack of spaces in the string argument of the find_in_set function.

Grant G
  • 77
  • 7
The Scrum Meister
  • 29,681
  • 8
  • 66
  • 64
5

Check out mysql's ORDER BY FIELD. I think it will do exactly what you want.

Glen Solsberry
  • 11,960
  • 15
  • 69
  • 94
  • 1
    Is this faster than FIND_IN_SET? I'd bet it is, since it doesn't have string parsing syntax like FIND_IN_SET. The syntax I believe is: `ORDER BY FIELD(id,1,3,2,9,7)` – Ciantic Dec 15 '11 at 21:47
  • Please add all clarification **to your answer** instead of linking to external ressources. The link is dead, which makes this whole answer useless – Nico Haase Aug 21 '23 at 08:02
2

Easy answer:

Instrument your data with another "ordering" int field and then ORDER BY that field. This should be all that's necessary most of the time. I've successfully done this where clients can bubble certain products up a featured list, etc. by applying low values like -1 or -99 into the ordering field.

Complex answer:

This would apply if you wanted to normalize that ordering, and if maybe you had another field as the second factor in the order, that's already in your main table. This would also help if you have other information associated with each ordering point, like a note. Or, if lots of tables are going to implement this arbitrary order, and you want to orchestrate/modify that ordering from one place.

What you'd do is place the "arbitrary" order in a table you can join and then ordering by that field:

SELECT t.*, o.ordering
FROM table_name AS t
LEFT JOIN table_name_ordering AS o ON t.ordering_id = o.id
ORDER BY o.ordering, t.other_field
zanlok
  • 1,608
  • 1
  • 16
  • 29