Return MySQL Results from an “IN” Query in the Correct Order

By marc • Apr 29th, 2008 • Category: MySQL

The scenario:

You have a list of ids in a particular order that you want to search for in a MySQL table. Perhaps the ids were returned in this order from a search index or something else. Whatever the case is, now you want to get the results in the same order as the ids that you supplied. However, you discover that MySQL actually returns the results in a different order. How exactly do you get it to order by the supplied list?

Let take an array of ids in a desired order:

4, 9, 1, 5

And our example query:

  1.  
  2. SELECT * FROM characters WHERE id IN (4, 9, 1, 5)

Desired Result:

  1.  
  2. ID      NAME
  3. =================================
  4. 4       Marty McFly
  5. 9       Dr. Emmett Brown
  6. 1       George McFly
  7. 5       Einstein

Actual Returned Result:

  1.  
  2. ID      NAME
  3. =================================
  4. 1       George McFly
  5. 4       Marty McFly
  6. 5       Einstein
  7. 9       Dr. Emmett Brown

As we can see, MySQL actually orders the results by the id, so we need to force it to order everything by our original list of ids. The secret is using an ORDER BY CASE syntax in the query. Here’s an example:

Revised Query:

  1.  
  2. SELECT *
  3.     FROM characters
  4. ORDER BY CASE id
  5.     WHEN 4 THEN 1
  6.     WHEN 9 THEN 2
  7.     WHEN 1 THEN 3
  8.     WHEN 5 THEN 4
  9.     ELSE 999
  10. END

By using the “ORDER BY CASE”, we are specifying what positions each of the returned results should appear in.

PHP Example

Here is an example of how you can create the query within PHP.

  1.  
  2. // create our array of ids to search for
  3. $search_ids = array(4, 9, 1, 5);
  4.  
  5. // create the case variable for query
  6. $case = '';
  7.  
  8. // loop through the search array
  9. foreach ($search_ids as $key => $value){
  10.  
  11.  // create our place number
  12.  $num = $key + 1;
  13.  
  14.     // add the id to the case string
  15.     $case .= " WHEN $value THEN $num ";
  16.  
  17. }
  18.  
  19. // convert ids to string
  20. $ids = implode(',', $ids);
  21.  
  22. // build the final query
  23. $sql = "SELECT *
  24.          FROM characters
  25.               WHERE
  26.                    id IN ($ids)
  27.               ORDER BY
  28.                    CASE id
  29.                         $case
  30.                    ELSE 999
  31.               END
  32. ";
  33.  
  34. // run the query….

marc is a Senior Software Engineer in Los Angeles, CA. He likes to receive comments :)
All posts by marc

Leave a Reply