How to get query result in same order as given in clause

Question

I have a table like below, id being the primary key

and have a query like below. This query is created in a php file from user input choices.

    select name from table where id in (1,5,3)

I get the result ("a", "c", "e") which I guess is normal because of the default primary key sort order. However I want result to be ordered in the same sequence as the "in" clause. So I want returned value to be ("a", "e", "c"). Is there any way to get it in mysql.

One way you can use union to join result the order selected from UI end — avisheks, May 09 '14 at 11:31
Yes jensgram. It is a dup. The answer from Raging Bull is also same as yours over there. I am curious why I didn't find it earlier, may be because all related keywords "mysql", "sort", "order", "in" are very generic. Thanks. — John, May 09 '14 at 11:46

Raging Bull · Accepted Answer · 2014-05-09T11:48:22.370

7

You can simply use FIELD():

select name from TableName where id in (1,5,3)
ORDER BY field(id,1,5,3)

Result:

NAME
a
e
c

See result in SQL Fiddle.

edited May 09 '14 at 11:48

answered May 09 '14 at 11:32

Raging Bull

18,593
13
50
55

2

I've never had to use something like that and I did't know that kind of sort with the field operator. Very useful. – fmgonzalez May 09 '14 at 11:38
1

This is brilliant. I never really expected it to be so easy. Thanks so much. – John May 09 '14 at 11:43
1

Not working with PostgreSQL – aagjalpankaj Apr 26 '18 at 08:33

score 2 · Answer 2 · answered May 09 '14 at 11:31

You can use a CASE operator to specify the order:

SELECT * FROM table
WHERE id IN (3,6,1,8,9)
ORDER BY CASE id WHEN 3 THEN 1
                 WHEN 6 THEN 2
                 WHEN 1 THEN 3
                 WHEN 8 THEN 4
                 WHEN 9 THEN 5
         END

in php u can do it like :

<?php

$my_array =  array (3,6,1,8,9) ;

$sql = 'SELECT * FROM table  WHERE id IN (3,6,1,8,9)';

$sql .= "\nORDER BY CASE id\n";
foreach($my_array as $k => $v){
    $sql .= 'WHEN ' . $v . ' THEN ' . $k . "\n";
}
$sql .= 'END ';

echo $sql;

?>

How to get query result in same order as given in clause

2 Answers2