SQL: Order on the basis of values of multiple columns-CodePudding

I have a table that includes 3 columns - chat, video, and call. They can have values 0 or 1.

Now I want to order results such that if all three (chat, video, and call) are 0, then those rows are in the last else they have random order.

What will be the SQL query in this case? I am using MySQL.

CodePudding user response：

You can use conditional ordering using a CASE clause in the ORDER BY clause.

For example:

select *
from t
order by case when chat   video   `call` = 0 then 1 else 0 end

If randon order is really important you can change the ordering as shown below:

select *
from t
order by case when chat   video   `call` = 0 
                 then 1   rand() 
                 else 0   rand() 
         end

CodePudding user response：

Just order them descending. The (0,0,0) tupples stay at the bottom

select *
from yourtable
order by chat desc, video desc, call desc

CodePudding user response：

Using the fact that column values can be only 0 or 1 and MySQL understands them in logical operations:

SELECT *
FROM tbl
ORDER BY CASE WHEN chat OR video OR call THEN RAND() 1 ELSE 0 END DESC