CODEWITHSUNDEEP
mysqlsql-order-by
swamprunner7
swamprunner7

Reputation: 1319

MySQL order by IN order

i have simple query:

SELECT data FROM table WHERE id IN (5, 2, 8, 1, 10)

Question is, how can i select my data and order it like in my IN.

Order must be 5, 2, 8, 1, 10.

Problem is that i have no key for order. IN data is from other query (1), but i need to safe order.

Any solutions?

(1) 

SELECT login 
FROM posts 
    LEFT JOIN users ON posts.post_id=users.id 
WHERE posts.post_n IN (
    2280219,2372244, 2345146, 2374106, 2375952, 2375320, 2371611, 2360673, 2339976, 2331440, 2279494, 2329266, 2271919, 1672114, 2301856
)

Thanx for helping, solutions works but very slow, maybe find something better later, thanx anyway

Upvotes: 4

Views: 182

Answers (5)

Bilal Akil
Bilal Akil

Reputation: 4755

http://sqlfiddle.com/#!2/40b299/6

I think that's what you're looking for :D Adapt it to your own situation.

Upvotes: 1

C3roe
C3roe

Reputation: 96306

SELECT data FROM table
  WHERE id IN (5, 2, 8, 1, 10)
  ORDER BY FIELD (id, 5, 2, 8, 1, 10)

http://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_field

Might be easier to auto-generate (because it basically just needs inserting the wanted IDs comma-separated in the same order a second time) than the other solutions suggested using CASE or a number of ID=x, ID=y ...

Upvotes: 1

Dmitriy Khaykin
Dmitriy Khaykin

Reputation: 5258

To do this dynamically, and within MySql, I would suggest to do the following:

  1. Create a temp table or table variable (not sure if MySql has these), with two columns:
OrderID mediumint not null auto_increment
InValue mediumint -(or whatever type it is)

  1. Insert the values of the IN clause in order, which will generate ID's in order of insertion

  2. Add a JOIN to your query on this temp table

  3. Change your Order By to be 

order by TempTable.OrderID
  1. Drop temp table (again, in SQL inside a stored proc, this is automatic, not sure about MySql so mentioning here for full disclosure)

This effectively circumvents the issue of you not having a key to order by in your table ... you create one. Should work.

Upvotes: 0

Matt Busche
Matt Busche

Reputation: 14333

You can use a CASE statement

SELECT data 
FROM table WHERE id IN (5, 2, 8, 1, 10)
ORDER BY CASE WHEN id = 5 THEN 1 WHEN id = 2 THEN 2 WHEN id = 8 THEN 3 WHEN id = 1 THEN 4 WHEN id = 10 THEN 5 END

Upvotes: 1

Explosion Pills
Explosion Pills

Reputation: 191749

The only way I can think to order by an arbitrary list would be to ORDER BY comparisons to each item in that list. It's ugly, but it will work. You may be better off sorting in whatever code you are doing the selection.

SELECT data FROM t1 WHERE id IN (5, 2, 8, 1, 10)
ORDER BY id = 10, id = 1, id = 8, id = 2, id = 5

The order is reversed because otherwise you would have to add DESC to each condition.

Upvotes: 1

Related Questions