10

When I select a set of rows from a table using IN, e.g.

SELECT x.y, x.z FROM x WHERE x.id IN (23, 55, 44, 12)

is there an SQL trick to get them back in the order given in the IN set?

So in the example, assuming x has rows with ids 23, 55, 44 and 12, those four rows would be returned in that order.

2 Answers2

12
SELECT x.y, x.z FROM x WHERE x.id IN (23, 55, 44, 12)
ORDER BY FIELD (x.id, 23, 55, 44, 12)
Steve
  • 5,771
  • 4
  • 34
  • 49
  • Is ORDER BY FIELD supported in MySQL 5.0? A: Yes it is! I just checked. –  Feb 27 '10 at 17:36
4

You can use FIND_IN_SET as:

SELECT x.y, x.z FROM x WHERE x.id IN (23, 55, 44, 12)
ORDER BY FIND_IN_SET(x.id,'23, 55, 44, 12');
codaddict
  • 445,704
  • 82
  • 492
  • 529
  • 1
    On the face of it, there's nothing to choose between your answer and Steve's. But which to accept? In a simple test I tried, ORDER BY FIELD was a bit more than twice as fast –  Feb 27 '10 at 17:52