CODEWITHSUNDEEP
mysqlsql
friction
friction

Reputation: 907

MySQL order by IN array

I have a MySQL script like this: SELECT id, name FROM users WHERE id IN (6,4,34)

The sequence in the IN(...) array is very important. Is it possible to get them in the given sequence?

Upvotes: 6

Views: 6922

Answers (4)

Lightning_young
Lightning_young

Reputation: 86

A complete example based on Chris Trahey answer.

$ids = array("table1",  "table2", "table3");
    
$sortVal = 1;
foreach ($ids as $id_val) {
    $cases[] = sprintf("WHEN '%s' THEN %u ", $id_val, $sortVal++);
}    
$order_by = 'ORDER BY CASE `tableName` ' . implode($cases) . ' END ASC';    

$result = mysqli_query( $con, "
        SELECT DISTINCT tableName 
        FROM `table` 
        $order_by");

Upvotes: 0

Chris Trahey
Chris Trahey

Reputation: 18290

You can use any expression in the ORDER BY clause, including a 'CASE':

ORDER BY CASE id 
  WHEN 6 THEN 1 
  WHEN 4 THEN 2 
  WHEN 34 THEN 3 
END ASC

If your list comes from the application programming layer, you might build this with the following (PHP here):

$sortVal = 1;
foreach($ids as $id_val) {
  $cases[] = sprintf('WHEN %i THEN %i', $id_val, $sortVal++);
}
$order_by = 'ORDER BY CASE id ' . implode($cases) . ' END ASC';

However, I'll mention that Joachim's answer is quite elegant :-)

Upvotes: 1

Danylo Mysak
Danylo Mysak

Reputation: 1512

Try

SELECT id, name FROM users WHERE id IN (6,4,34) order by FIELD(id,6,4,34)

Upvotes: 3

Joachim Isaksson
Joachim Isaksson

Reputation: 181097

You can use the MySQL FIELD function to keep it compact;

SELECT id, name 
FROM users 
WHERE id IN (6, 4, 34)
ORDER BY FIELD(id, 6, 4, 34);

Upvotes: 16

Related Questions