CODEWITHSUNDEEP
mysqlsql
Tushar Agrawal
Tushar Agrawal

Reputation: 93

SQL: Order on the basis of values of multiple columns

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.

Upvotes: 0

Views: 39

Answers (3)

LukStorms
LukStorms

Reputation: 29647

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

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

But that sort them all.

But you can sort also by nulling the true OR's
(since NULL stays on top when sorted ascending)

select *
from yourtable
order by nullif(chat or video or `call`,1)

Upvotes: 2

The Impaler
The Impaler

Reputation: 48780

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

Upvotes: 3

PM 77-1
PM 77-1

Reputation: 13334

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

Upvotes: 1

Related Questions