Lucio Crusca - 1 year ago 77
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.

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
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download