CODEWITHSUNDEEP
mysqlsql
TiMESPLiNTER
TiMESPLiNTER

Reputation: 5889

ORDER BY two conditions

I try to achieve the following: I have messages with different colors (red, orange, green). Now I like to sort my resultset from the messages posted in the last three ours by the following condition:

So here's the SQL I've so far. But this does not handle the case when a green message has been posted most recent:

SELECT ID, color
FROM mod_cc_stream_entry
WHERE DATE_ADD(status_date, INTERVAL 3 HOUR) >= NOW()
ORDER BY FIELD(COALESCE(color, 'green'), 'red', 'orange', 'green')

I could run two statements. First to check if the newest message is green and if not run a second statement to get the order I like (statement above).

But I like to know if there is a possbility to do that in one statement.

UPDATE SQLFiddle

Upvotes: 0

Views: 293

Answers (2)

John Ruddell
John Ruddell

Reputation: 25842

at a first guess you could try something like a conditional order by

QUERY:

SELECT ID, color
FROM mod_metanet_cc_stream_entry
JOIN (
    SELECT MAX(status_date) as new_status 
    FROM mod_metanet_cc_stream_entry
) temp
WHERE DATE_ADD(status_date, INTERVAL 3 HOUR) >= NOW()
ORDER BY
    CASE 
        WHEN status_date = new_status AND color = 'green' THEN 1
        WHEN color = 'red' THEN 2
        WHEN color = 'orange' THEN 3
        WHEN color is null THEN 8
        ELSE 4
    END

I would need some data to test it though

OUTPUT:

+----+--------+
| ID | COLOR  |
+----+--------+
| 20 | red    |
| 26 | red    |
| 17 | red    |
| 23 | red    |
| 16 | orange |
| 24 | orange |
| 21 | orange |
| 27 | orange |
| 18 | green  |
| 19 | green  |
| 22 | NULL   |
| 25 | NULL   |
+----+--------+

Upvotes: 3

Sandeepan Nath
Sandeepan Nath

Reputation: 10284

Using inputs from John Ruddell's answer, I am getting the last time first using a query and then using it in the order by clause.

SET @const = 3;
SET @last_time = (SELECT status_date FROM mod_metanet_cc_stream_entry WHERE DATE_ADD(status_date, INTERVAL @const HOUR) >= NOW() ORDER BY status_date DESC LIMIT 1);

SELECT ID, color, status_date
FROM mod_metanet_cc_stream_entry
WHERE DATE_ADD(status_date, INTERVAL @const HOUR) >= NOW()
ORDER BY
    CASE 
        WHEN (status_date = @last_time AND color = 'green') THEN 1
        WHEN color = 'red' THEN 2
        WHEN color = 'orange' THEN 3
        ELSE 4
    END;

Upvotes: 0

Related Questions