How to make MYSQL query results ORDER BY conditions order?


Problem :

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?



Solution :

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.


Mysql Tutorials

Mysql Howto..

how to arrange mysql query results starting from a particular row

How to create mysql nested loop in stored procedure

how to get the sum of values in the different field in mysql?

How to store European Currency in MySQL?

How to select last date in 2 record in Mysql

How to show MySQL data by a managed bean?

How to group all string values to one cell in MySQL?

How to get details from another table using comma operator in mysql

How to take column backup for a table in mysql [closed]

how to store keywords/tags

Bash: how to easily edit one line in a 5.4G SQL dump [closed]

mysql, how to drop tables that the table name starts with digits

how to get id of last inserted row [duplicate]

How to make Rails generate 'schema.rb' with bigint support for MySQL?

How to write a script to insert large amounts of data in mysql? [closed]

How to UPDATE mysql using implode array

Poor Performance from MySQL JOIN - How to Make Improvements?

Flask and Python how to make search engine for data from mysql database

How to declare a variable in MySQL for a normal query?

How to retrieve all records after one with an ID in symfony?

How to show only one result from each type in a mysql array php

Creating a leaderboards, how would I go about displaying rank/position?

MYSQL Query - how to sum data by total, current year and last 12 months in the same query (1 sum by column)?

on php page how to insert row after nth row using mysql

How to sort month years from mysql to php

How to select ALL usernames from my members table?

How To Construct This Query To Replace A Row With A Matching Element

How to implement the code in codeigniter

How to replace output of SELECT statemnt in Mysql?

How do I add foreign key constraints in my innodb table without indexing the columns?