CODEWITHSUNDEEP
sqlmysql
lovespring
lovespring

Reputation: 19559

MySQL 'IN' clause and the returned record set order

For example: select * from T where T.id IN(4,78,12,45)

I want the returned record set just order by the position in the 'IN' clause. How can I do this?

Upvotes: 7

Views: 1579

Answers (3)

Robin Day
Robin Day

Reputation: 102478

You cannot order by the IN clause. You must provide a separate ORDER BY clause. A way of doing this is as follows by building up a case statement in the ORDER BY. It's not pretty though.

SELECT
    *
FROM
    T
WHERE
    T.Id IN(4, 78, 12, 45)
ORDER BY
    CASE
        WHEN T.Id = 4 THEN 1
        WHEN T.Id = 78 THEN 2
        WHEN T.Id = 12 THEN 3
        WHEN T.Id = 45 THEN 4
    END

Upvotes: 0

Paul Dixon
Paul Dixon

Reputation: 300845

You could do it using FIND_IN_SET, e.g.

SELECT * FROM T WHERE T.id IN(4,78,12,45)
ORDER BY FIND_IN_SET(T.id,'4,78,12,45');

While you do have to duplicate the list, if you're generating the query in code this isn't a huge problem.

Upvotes: 20

Welbog
Welbog

Reputation: 60408

In the general case, you can't. SQL doesn't guarantee order unless you use the ORDER BY clause, and it can't be tied into the contents of an IN statement.

However, if you can build a temporary table that orders the values you're selecting from, you can join on that table and order by it.

For example, you have a temporary table that contains something like the following:

id  | order
----+------
4   | 1
78  | 2
12  | 3
45  | 4

Then you can order it like this:

SELECT T.*
FROM T
INNER JOIN temp
ON T.id = temp.id
ORDER BY temp.order ASC

Upvotes: 1

Related Questions