CODEWITHSUNDEEP
sqlmysqlsql-order-by
Surla
Surla

Reputation: 63

Order of "WHERE field IN" SQL query?

I am using the following SQL to select records from MySQL database:

SELECT * FROM cms_product WHERE id IN (3,22,1);

The results order equals "ORDER BY id ASC", so as in example records 1,3,22 are returned. How can I get them ordered in the exact way as typed in IN clause? So ordered as 3,22,1 ? Thank you.

Upvotes: 6

Views: 6728

Answers (5)

Daniel Vassallo
Daniel Vassallo

Reputation: 344301

It may not be very neat, but you may want to do the following:

SELECT    * 
FROM      cms_product 
WHERE     id IN (3, 22, 1)
ORDER BY  id = 3 DESC, id = 22 DESC, id = 1 DESC;

Test case:

CREATE TABLE cms_product (id int, value int);

INSERT INTO cms_product VALUES (1, 100);
INSERT INTO cms_product VALUES (3, 200);
INSERT INTO cms_product VALUES (22, 300);

Result:

+------+-------+
| id   | value |
+------+-------+
|    3 |   200 |
|   22 |   300 |
|    1 |   100 |
+------+-------+
3 rows in set (0.02 sec)

UPDATE:

The ... ORDER BY FIELD (id, 3, 22, 1) as suggested by @Dave and @OMG Ponies returns the same results, and is actually much neater.

Upvotes: 1

OMG Ponies
OMG Ponies

Reputation: 332571

Numerous options -

CASE:

Preferred, being ANSI-92 it's portable to other databases.

  SELECT * 
    FROM cms_product 
   WHERE id IN (3,22,1)
ORDER BY CASE id
           WHEN 3 THEN 1
           WHEN 22 THEN 2
           WHEN 1 THEN 3
         END

FIND_IN_SET:

  SELECT * 
    FROM cms_product 
   WHERE id IN (3,22,1)
ORDER BY FIND_IN_SET(id, '3,22,1');

FIELD:

  SELECT * 
    FROM cms_product 
   WHERE id IN (3,22,1)
ORDER BY FIELD(id, 3, 22, 1);

Reference:

Upvotes: 8

Sculea Vasile
Sculea Vasile

Reputation: 31

ORDER BY FIELD(id, '3', '22', '1') ???

Upvotes: 0

Dave
Dave

Reputation: 506

Try using ORDER BY FIELD, such as:

SELECT * FROM cms_product WHERE id IN (3,22,1) ORDER BY FIELD (id, 3, 22, 1);

source here

Upvotes: 6

Femaref
Femaref

Reputation: 61437

You can't, it has to be either ASC or DESC.

Upvotes: 0

Related Questions