Lucio Crusca Lucio Crusca - 1 month ago 10
MySQL Question

ORDER BY first OR condition true

I have the folloing table in a MySQL 5.6+ database

+----+-------+-------+--------------+--------------+
| id | mood1 | mood2 | mood1_visual | mood2_visual |
+----+-------+-------+--------------+--------------+
| 1 | 5 | 7 | 5c | 7a |
| 2 | 5 | 7 | 5b | 7b |
| 3 | 5 | 7 | 5c | 7d |
| 4 | 5 | 8 | 5a | 8a |
| 5 | 5 | 7 | 5c | 7a |
| 6 | 5 | 8 | 5b | 8a |
+----+-------+-------+--------------+--------------+


I need to select rows where

(`mood1_visual`='5c' AND `mood2_visual`='7a') OR
(`mood1`=5 AND `mood2`=7 AND (`mood1_visual`<>'5c' OR `mood2_visual`<>'7a'))


In the example above those would be the rows 1,2,3 and 5. However I need them sorted with the ones that match the first part of the
WHERE
clause before the others, i.e. I need them sorted 1,5,2,3, because row 1 and row 5 satisfy the first part of the
WHERE
clause

(`mood1_visual`='5c' AND `mood2_visual`='7a')


How do I tell that in the
ORDER BY
clause? Is there anything like

ORDER BY THE ONES THAT MATCH CONDITION FIRST THEN THE OTHERS ?


Please note that 5,1,2,3 or 1,5,3,2 or 5,1,3,2 are all valid sortings for my needs, just in case that made the solution easier.

Answer

MySQL can order by arbitrary expressions, e.g.

ORDER BY (foo = bar)

The boolean result of that will be typecast to an integer 0 or 1, so if you want all of the "equal" values first (1), then

ORDER BY (foo = bar) DESC

and if you want all of the false (0) values first, then

ORDER BY (foo = bar) ASC