MySql order by related with today's date

Question

I have a date column and I am using order by clause.

I want to show the records near to today's date at top and then all records with the past at bottom.

I want to do this with single query.

I tried this

SELECT *, DATEDIFF(`date`, CURDATE()) AS diff  FROM `post` order by diff

Problem with this query is this will show records with past first and if I use descending then the records far from today will be on top.

How I can achieve this?

Any help will be appreciated.

Answer 1

You should be able to do it something like:

SELECT *, DATEDIFF(`date`, CURDATE()) AS diff  FROM `post`
order by CASE WHEN diff < 0 THEN 1 ELSE 0 END, diff

Which will force any dates in the past to sort after the current date or dates in the future.

Answer 2

Try this

   SELECT *, DATEDIFF(`date`, CURDATE()) AS diff  FROM `post` order by ABS(diff)