MySQL sort order by array value

Question

I need to run a MySQL query where the order is determined by an array value.

My array is variable but the values in the array correspond to a field in my DB table called 'ID' so I want the result to be returned in the ID order 9, 1, 4.

Array ( [0] => 9 [1] => 1 [2] => 4 )

Is this possible in MySQL or would it be possible to sort the MySQL $result using the array after? You can assume the only values being returned are those in the array.

lanzz · Accepted Answer · 2011-11-08 18:16:08Z

111

ORDER BY field(id, 9, 1, 4);

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

answered Nov 8, 2011 at 18:16

lanzz

43.4k12 gold badges93 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Coz · Accepted Answer · 2020-02-03 02:37:13Z

4

You want to get a list of items with the ids 5, 2, 1, 3 and output them in the same order. However, just running a select query will return the items in the order 1, 2, 3, 5.

To order the results correctly you need to build a list of ORDER BY items describing the weights of the ids. This will look like

ORDER BY id = 5 DESC, id = 2 DESC, id = 1 DESC, id = 3 DESC

edited Feb 3, 2020 at 2:37

Coz

1,99325 silver badges22 bronze badges

answered Jun 8, 2013 at 6:32

hari jee

675 bronze badges

Collectives™ on Stack Overflow

MySQL sort order by array value

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related