Reputation: 907
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
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
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
Reputation: 1512
Try
SELECT id, name FROM users WHERE id IN (6,4,34) order by FIELD(id,6,4,34)
Upvotes: 3
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