CODEWITHSUNDEEP
mysqldatesortingcase
shayanmalinda
shayanmalinda

Reputation: 2267

MYSQL sort date by future dates first in ASC and old dates then in DESC

I want to sort dates in MYSQL in a way such that, future dates will be sorted first by in ASC order and, then then old dates last in DESC order. Here is the query I used to do, but both date types (future and old) are sorted out in ASC order. How can I fix this?

SELECT id, end_date FROM employee 
    ORDER BY CASE WHEN DATE(date) > DATE(NOW())
        THEN 0 
        ELSE 1 END, date ASC

Upvotes: 1

Views: 557

Answers (1)

forpas
forpas

Reputation: 164099

First, sort by the boolean expression date <= CURRENT_DATE so that all future dates are on the top and then sort by the absolute difference to the current date:

SELECT *
FROM employee 
ORDER BY date <= CURRENT_DATE,
         ABS(DATEDIFF(date, CURRENT_DATE));

See the demo.

Upvotes: 3

Related Questions