MySQL: Specifying the order I'd like rows returned in

I have a SQL statement something along thes开发者_运维知识库e lines:

SELECT * FROM `table` WHERE some_column IN(1,58,22,9);

What I would like is to return the rows in the same order as the some_column values are specified, i.e. 1 before 58 before 22 before 9. The problem is that I have no column that, when sorted, will produce this specific order of rows.

Is there any way I can achieve this?

Use the FIND_IN_SET function:

  SELECT * 
    FROM `table` 
   WHERE some_column IN(1,58,22,9)
ORDER BY FIND_IN_SET(some_column, '1,58,22,9')

You can use a case to achieve pretty much any sort order:

select  *
from    TheTable
where   some_column in (1,58,22,9)
order by
      case some_column
          when 9 then 1
          when 22 then 2
          when 58 then 3
          when 1 then 4
      end

There's a couple of good solutions here already for ordering using the SQL statement. However taking such an approach has the drawback that it is inflexible: every time you need to select a different set of values you have to modify the SQL statement.

That may be fine if you're simply after a quick and dirty query to analyse data, but if you are likely to need to re-run the query, or modify it, or indeed this is in any sort of production environment whatsoever, then a better solution is to use a sort table. This should contain two columns - your source and your sort value - then write SQL that does the join and returns values ordered by the sort column.

For example:

TheSortTable
SomeColumn SortValue
9          1
22         2
58         3
1          4

The your sql is

SELECT SomeColumn, SomeValue FROM TheTable
INNER JOIN TheSortTable on TheTable.SomeColumn = TheSortTable.SomeColumn 
WHERE SomeColumn IN(1,58,22,9)
ORDER BY SortValue

In practical terms this is a far superior to explicitly coding your solution directly into SQL for anything other than a strictly once-off query (and indeed, the general philosophy of the approach is one worth adopting more widely).