complex ORDER BY with clauses

Question

I'm having problems trying to order the results from a MySQL query.

The table contains user information, notably:

• user_status (an integer)
• user_verified (boolean)
• last_login (date)

The ORDER BY clause should group users into 6 main groups and within each group order them by the last_login DESC.

Thus I'm trying to get (in pseudo code):

first show users: user_status = 1 AND user_verified = true, last_login DESC
then: user_status = 1 AND user_verified = false, last_login DESC
then: user_status = 2 AND user_verified = true, last_login DESC
then: user_status = 2 AND user_verified = false, last_login DESC
then: user_status = 0 AND user_verified = true, last_login DESC
then: user_status = 0 AND user_verified = false, last_login DESC

I'm having problems putting this together into a coherent ORDER BY clause so any help would be appreciated!

Answer 1

You can use boolean expressions in the ORDER BY clause, because they are evaluated as 1 for true or 0 for false.

In your case you can simplify your requirement to:

ORDER BY user_status = 1 DESC,
         user_status = 2 DESC,
         user_status = 0 DESC,
         user_verified DESC,
         last_login DESC;

or:

ORDER BY CASE user_status 
           WHEN 1 THEN 1
           WHEN 2 THEN 2
           WHEN 0 THEN 3
         END,
         user_verified DESC,
         last_login DESC;

or with FIELD() function:

ORDER BY FIELD(user_status, 1, 2, 0),
         user_verified DESC,
         last_login DESC;