CODEWITHSUNDEEP
mysqlsql-order-by
Fredrik
Fredrik

Reputation: 3302

MySQL Order by DESC if column = X, otherwise ASC

How do I order query by start_date DESC if finished = 1, otherwise start_date ASC. Right now it looks as this:

SELECT game_id, 
    event_id, 
    start_date, 
    best_of, 
    home_team_id, 
    away_team_id, 
    home_value, 
    away_value, 
    home_result, 
    away_result, 
    stream_url, 
    stats_url, 
    comments, 
    finished 
FROM betting_games 
ORDER BY finished ASC, 
    start_date ASC 
LIMIT 5

Upvotes: 0

Views: 196

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72235

Here's one way:

SELECT *
FROM betting_games
ORDER BY finished ASC, 
         CASE 
            WHEN finished =  1 THEN - 1 * UNIX_TIMESTAMP(start_date)
            ELSE UNIX_TIMESTAMP(start_date)
         END ASC

You cannot return DESC or ASC from a CASE expression. Using UNIX_TIMESTAMP, date field start_date is converted into an integer, that can be used to store in descending order (once negated).

Demo here

Upvotes: 3

Related Questions