complex ORDER BY with clauses-CodePudding

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!

CodePudding user response：

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;

CodePudding user response：

Using UNION is more straight forward if you find the expressions from a single ORDER BY clause too complex. It's less prone to logical errors. e.g:

create table tablename (user_status int, user_verification bool,last_login datetime);
insert tablename values(0,true,now()); -- 5th order by
insert tablename values(2,false,now()); -- 4th order by
insert tablename values(1,true,now()); -- 1st order by
insert tablename values(0,true,now()); -- 5th order by
insert tablename values(1,false,now()); -- 2nd order by
insert tablename values(0,false,now()); -- 6th order by
insert tablename values(2,true,now()); -- 3rd order by
insert tablename values(2,false,now()); -- 4th order by
insert tablename values(1,false,now()); -- 2nd order by
insert tablename values(0,false,now()); -- 6th order by
insert tablename values(1,true,now()); -- 1st order by
insert tablename values(2,true,now()); -- 3rd order by
(select * from tablename  where user_status = 1 AND user_verified = true)
union
(select * from tablename  where user_status = 1 AND user_verified = false)
union
(select * from tablename  where user_status = 2 AND user_verified = true)
union
(select * from tablename  where user_status = 2 AND user_verified = false)
union
(select * from tablename  where user_status = 0 AND user_verified = true)
union
(select * from tablename  where user_status = 0 AND user_verified = false)
;