How to make MYSQL query results ORDER BY conditions order?
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?
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
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.
- A Linux, Apache, MySQL, PHP (LAMP) stack is a combination of open source software that is typically installed together to enable a server to host dynamic […]
- MySQL is an the most popular open-source database management system, in most of hosting company its has been bundle by LAMP(Linux, Apache, MySQL, PHP/Python/Perl) stack. Another alternative […]
- MySQL is the world’s most popular open-source database. Despite its powerful features, MySQL is simple to set up and easy to use. Below are some instructions […]