CODEWITHSUNDEEP
mysqlsql
Rupesh Pawar
Rupesh Pawar

Reputation: 1917

MySql order by related with today's date

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.

Upvotes: 4

Views: 9947

Answers (6)

Tony Vlcek
Tony Vlcek

Reputation: 456

Combining @hkutluay' answer and the accepted answer from @Damien_The_Unbeliever yields the expected results where the posts go from upcoming closes to today then past post again closes to today first:

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

Example result:

Assuming that today is 1.1.2022

P1 1.1.2022
P2 1.2.2022
P3 31.12.2021
P4 31.11.2021
...

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239714

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.

Upvotes: 9

Rich O&#39;Kelly
Rich O&#39;Kelly

Reputation: 41767

Since you do not want results that are too far in the future, simply remove these results from the result set:

SELECT *, DATEDIFF(`date`, CURDATE()) AS diff FROM `post` 
WHERE `date` < date_add(CURDATE(), INTERVAL 1 DAY)
ORDER BY diff DESC

Upvotes: 0

Randy
Randy

Reputation: 16677

why no tjust plain old date order?

 SELECT *, DATEDIFF(`date`, CURDATE()) AS diff  
 FROM `post ORDER BY date DESC;

Upvotes: 0

hkutluay
hkutluay

Reputation: 6944

Try this

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

Upvotes: 3

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79949

You need to ORDER BY Descendly like this:

 SELECT *, DATEDIFF(`date`, CURDATE()) AS diff  
 FROM `post ORDER BY diff DESC;

Upvotes: 1

Related Questions