CODEWITHSUNDEEP
mysqlsql
Sruj
Sruj

Reputation: 1207

How to sort by column Date that is greater than DATE(NOW())?

I have date_end column that I need to sort where it's only important to top rows with date greater than today.

So give date greater than today = "1", smaller = "0" and sort that.

So with below data table:

+----+------------+ 
| id | date_end   |  
+----+------------+ 
| 1  | 31-01-2018 |  
+----+------------+ 
| 2  | 01-05-2019 |  
+----+------------+ 
| 3  | 31-05-2018 |  
+----+------------+ 
| 4  | 31-07-2019 |  
+----+------------+ 
| 5  | 31-04-2020 |  
+----+------------+ 
| 6  | 31-08-2019 |  
+----+------------+

I need to virtually add "actual" column and sort by it:

+----+------------+--------+
| id | date_end   | actual |
+----+------------+--------+
| 1  | 31-01-2018 | 0      |
+----+------------+--------+
| 2  | 01-05-2019 | 0      |
+----+------------+--------+
| 3  | 31-05-2018 | 0      |
+----+------------+--------+
| 4  | 31-07-2019 | 1      |
+----+------------+--------+
| 5  | 31-04-2020 | 1      |
+----+------------+--------+
| 6  | 31-08-2019 | 1      |
+----+------------+--------+

Something like: ORDER BY CASE WHEN date_end > DATE(NOW()) THEN 1 ELSE 2 END

Upvotes: 0

Views: 1033

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

In MySQL, you can take advantage of the fact that boolean expressions can be used directly. So:

order by (date_end > curdate()) desc

The "true" > "false", so desc puts the true values first.

Upvotes: 1

dfundako
dfundako

Reputation: 8324

Then do exactly as you suggested: 

ORDER BY CASE WHEN date_end > curdate() THEN 1 ELSE 0 END

Upvotes: 2

Related Questions