Geek asks and answers

MySQL sorts after argument in IN ()

I have a string containing many identifiers corresponding to my database. How:

1,2,3,4,5

Then I make a mySQL query to select all of these rows:

SELECT * FROM `table` WHERE `id` IN (".$myIDs.")

I want mySQL to return rows in the order that they are in my IN () selection.

So, if I had

2,1,3,4,5

I would have # 2 as the first line when selected in PHP. Without ORDER BY, it looks like it returns the lowest id first.

Let me know if you need additional exlpanation.

+3
source share
4 answers

You can do this with the function FIELD():

SELECT * FROM `table` WHERE `id` IN (2,1,3,4,5) ORDER BY FIELD(`id`, 2,1,3,4,5) DESC

I.e:

SELECT
  *
FROM
  `table`
WHERE
  `id` IN (".$myIDs.")
ORDER BY
  FIELD(`id`, ".$myIDs.") DESC

More on this blog post: Sorting MySQL rows using column values .

+5

order by case ... end, case end PHP:

select
    *    
from
    table
where
    id in (2, 1, 3, 4, 5)
order by
    case id
        when 2 then 1
        when 1 then 2
        when 3 then 3
        when 4 then 4
        when 5 then 5
    end asc
0

do you want find_in_set function

$list = "1,2,....";
$sql = "select * from table where id in($list) order by find_in_set($id, '$list')";

another (possibly faster) option is to sort in php:

$list = array(2, 3, ...);
$s    = implode(',', $list);
$sth  = mysql_query("select * from table where id in($s)");

$rows = array();
while($r = mysql_fetch_object($sth))
    $rows[$r->id] = $r;

$sorted_rows = array();
foreach($list as $id)
   $sorted_rows[] = $rows[$id];
0
source

Another option, though FIELD, is probably the path in your case:

SELECT * FROM `table` WHERE `id` IN (2,1,3,4,5) ORDER BY `id` = 2 DESC, `id` = 1 DESC,`id` = 3 DESC, `id` = 4 DESC, `id` = 5 DESC
0
source

More articles:


All Articles