Geek Answers Handbook

Order MySQL on request

I have a simple query:

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

The question is how can I select my data and order it, as in my IN.

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

The problem is that I do not have a key for the order. IN from another request (1), but I need a secure 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 help, solutions work, but very slow, maybe finding something better later than anyway

+4
source share
5 answers

The only way I can think of ordering an arbitrary list is to compare ORDER BY with each item in this list. It is ugly, but it will work. You might be better off sorting any code you make.

 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 canceled, because otherwise you need to add DESC to each condition.

+1
source

You can use the 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
+1
source 
 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

It’s easier to auto-generate (because basically you just need to insert the required identifiers, separated by commas in the same order the second time) than other solutions offered using CASE or several ID = x, ID = y ...

+1
source

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

I think what you are looking for: D Adapt it to your own situation.

+1
source

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

  • Create a temporary table or table variable (not sure what MySql has), with two columns:
 OrderID mediumint not null auto_increment InValue mediumint -(or whatever type it is)

  • Paste the values ​​of the IN clause into the order that the identifier will generate in insertion order

  • Add JOIN to your query in this temporary table

  • Change Your Order By

 order by TempTable.OrderID
  • Temporary topic table (again, in SQL inside the stored procedure, it is automatically, not sure that MySql is mentioned here for full disclosure)

This effectively circumvents the problem that you do not have a key for the order in your desk ... you create it. Must work.

0
source

More articles:


All Articles