CODEWITHSUNDEEP
mysql
FoolishWoman
FoolishWoman

Reputation: 15

ORDER BY IF statement in MYSQL, if result is less than

I have a table that looks somewhat like this ordered by received_date DESC

+---------+---------------+------------+---------------+
|  name   |  assignment   |  due_date  | received_date |
+---------+---------------+------------+---------------+
| Luke    | Vacuum        | 2018-06-14 | 2018-01-04    |
| Mark    | Wash the car  | 2016-01-01 | 2018-01-03    |
| Matthew | Do the dishes | NULL       | 2018-01-02    |
| John    | Walk the dog  | 2019-01-01 | 2018-01-01    |
+---------+---------------+------------+---------------+

I'm trying to return rows by due_date but only if the due_date was in the past or is tomorrow, otherwise keep it ordered by the received_date. For example, I'd like the above table to end up as

+---------+---------------+------------+---------------+
|  name   |  assignment   |  due_date  | received_date |
+---------+---------------+------------+---------------+
| Mark    | Wash the car  | 2016-01-01 | 2018-01-03    | due_date in the past
| Luke    | Vacuum        | 2018-06-14 | 2018-01-04    | due_date tomorrow
| Matthew | Do the dishes | NULL       | 2018-01-02    | rest sorted by received_date DESC
| John    | Walk the dog  | 2019-01-01 | 2018-01-01    |
+---------+---------------+------------+---------------+

First I tried

SELECT *, DATEDIFF(due_date,CURDATE()) AS due 
FROM table 
ORDER BY IF(due < 2, due_date, received_date) DESC, received_date DESC

But this doesn't work for negative numbers, so I tried turning the negatives into a zero:

SELECT *, IF(DATEDIFF(due_date,CURDATE()) > 0, DATEDIFF(due_date,CURDATE()), 0) AS due  
FROM table 
ORDER BY IF(due < 2, due_date, received_date) DESC, received_date DESC

But any positive number between due_date and now (like John's) is added to the top of the list, and ends up looking like

+---------+---------------+------------+---------------+
|  name   |  assignment   |  due_date  | received_date |
+---------+---------------+------------+---------------+
| John    | Walk the dog  | 2019-01-01 | 2018-01-01    |
| Mark    | Wash the car  | 2016-01-01 | 2018-01-03    |
| Luke    | Vacuum        | 2018-06-14 | 2018-01-04    |
| Matthew | Do the dishes | NULL       | 2018-01-02    |
+---------+---------------+------------+---------------+

Upvotes: 1

Views: 38

Answers (2)

Manojkumar B
Manojkumar B

Reputation: 216

Try this:

SELECT *, IF(DATEDIFF(due_date, CURDATE()) > 0, 1, IF(DATEDIFF(due_date, CURDATE()) < 0, -1, 0)) as tmp_col FROM `to_do` order by tmp_col asc, `received_date` desc;

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521409

Try this logic:

SELECT *
FROM yourTable
ORDER BY
    CASE WHEN DATEDIFF(due_date, CURDATE()) <= 1
         THEN due_date
         ELSE received_date END;

However, I get a different output than what you expect:

enter image description here

Demo

Upvotes: 1

Related Questions