MySQL sort after argument in IN()

Question

I have string containing a lot of IDs corresponding to my database. Like:

1,2,3,4,5

I then do a mySQL query to select all those rows:

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

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

So if I instead had

2,1,3,4,5

I would have #2 as first row, when fetching in PHP. Without any ORDER BY it looks like it return the lowest ID first.

Let me know if you need further exlpanation.

score 6 · Accepted Answer · answered Jun 09 '11 at 11:24

6

You should be able to do this via the FIELD() function like so:

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

That is:

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

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

answered Jun 09 '11 at 11:24

jensgram

31,109
6
81
98

@jensgram I tried your query but I get output in descending or ascending order my query is `SELECT * FROM tbl_questions WHERE question_level IN (1,2,1,2,3,2,4,2,3,2) ORDER BY FIELD(question_level,1,2,1,2,3,2,4,2,3,2);` But I am getting result in like 1,1,1,2,2,3,3,3 – Nagesh Katke Nov 08 '17 at 13:27
@NageshKatke I would expect `FIELD` to take unique values (I have a hard time comprehending how one would otherwise use it for ordering.) The distinct values you've provided are `1, 2, 3, 4` – jensgram Nov 08 '17 at 18:46
@jensgram what would be the solution for duplicate values – Nagesh Katke Nov 09 '17 at 04:30
@NageshKatke Sounds like a separate question to me :) – jensgram Nov 09 '17 at 06:15
@jensgram I already asked https://stackoverflow.com/questions/47194874/fetch-record-in-order-with-in-clause-having-duplicate-values-in-in-clause – Nagesh Katke Nov 09 '17 at 06:33

score 0 · Answer 2 · answered Jun 09 '11 at 11:25

You might use order by with case ... end, though part between case and end should be generated by 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

score 0 · Answer 3 · answered Jun 09 '11 at 11:25

you want the find_in_set function

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

another (probably faster) option is to do sorting 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];

score 0 · Answer 4 · answered Jun 09 '11 at 11:34

0

Another option, though FIELD is probably the way to go 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

answered Jun 09 '11 at 11:34

Aaron W.

9,254
2
34
45

But that would require I know the length of $myIDs - or I loop it in PHP and generate SQL? – jack Jun 09 '11 at 11:37
Yes you'd have to loop through your array and build the SQL. `FIELD` is probably the way to go, just putting another option out there. – Aaron W. Jun 09 '11 at 11:48

MySQL sort after argument in IN()

4 Answers4

Linked

Related