How to make MYSQL query results ORDER BY conditions order?

All we need is an easy explanation of the problem, so here it is.

My query string is like:

SELECT ... FROM maintable
LEFT JOIN table1 on (maintable.id = table1.idx)
LEFT JOIN table2 on (table1.idy = table2.idy)
LEFT JOIN table3 on (table2.idz = table3.idz)
WHERE (condition1 OR condition2 OR condition3)
AND maintable.status = static

//condition1 & condition2 & condition3 are kind of
table3.idz = 101, table3.idz = 3, maintable.id IN (1,2,3,4), and so on

For the results I want entries that meet condition1 to be returned first, then entries that meet condition2, and finally entries that meet condition3. Any ideas?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

To get the sorting in the order you want, use your conditions in the ORDER BY, but use DESC after them.

SELECT *
FROM person
WHERE (condition1 OR condition2 OR condition3)
AND maintable.status = static
ORDER BY
    condition1 DESC,
    condition2 DESC,
    condition3 DESC

If this doesn’t work because your query is more complex, then you can use boolean logic to change your query (A OR B OR C) AND D into (A AND D) OR (B AND D) OR (C AND D) then you can use the following query:

SELECT *
FROM person
WHERE (condition1 OR condition2 OR condition3)
AND maintable.status = static
ORDER BY
    condition1 AND static DESC,
    condition2 AND static DESC,
    condition3 AND static DESC

The AND static is not necessary here because all rows return it, but in a more complex example (where you also return some rows which are not static) then you would have to do it in this way.

Method 2

This should work:

ORDER BY condition1, condition2, condition3

for example

ORDER BY (weight > 500), (height > 3), (height < 2)

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply