1

How to fetch data from DB order by datetime closest to now or current date.

Below is my query, this will give idea about table structure.

SELECT o.id,
       p.price_quoted,
       p.discount,
       p.offer_price,
       p.amount_paid,
       o.user_id,
       s.subject,
       o.page_count,
       o.deadline,
       o.order_status,
       u.timezone
  FROM orders o
  JOIN payment p
    ON p.order_id = o.id
  JOIN subjects s
    ON s.id = o.subject_id
  JOIN users u
    ON u.id = o.user_id
ORDER BY o.deadline

ORDER BY o.deadline is doing the work but I need to handle the case where deadline has already passed.

Suppose today's date is "2021-05-08" and in my database I have 3 records as below:

deadline
2021-05-01
2021-05-22
2021-05-10

I want to get as below from Database:

deadline
2021-05-10
2021-05-22
2021-05-01

As 2021-05-01 is past, so it should come in last and future dates 2021-05-10, 2021-05-22 should be sorted as closest to today's date.

I am using MySQL and PHP.

Thanks in advance.

El_Vanja
  • 3,660
  • 4
  • 18
  • 21
Aj06
  • 79
  • 1
  • 15
  • 3
    Does this answer your question? [SQL Query to show nearest date?](https://stackoverflow.com/questions/6186962/sql-query-to-show-nearest-date) – El_Vanja May 07 '21 at 19:45
  • If you're still struggling, see https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query – Strawberry May 08 '21 at 07:28
  • @El_Vanja this does not answer my question. Using this it will give past events with sorted as future ones, but I want past events to be sorted it should be displayed at the end, please see my example again, thanks – Aj06 May 08 '21 at 14:21
  • 1
    I missed that part. So you're not actually looking to sort by closest, you're looking to sort by something custom. Might want to do that sorting on the PHP side. If you still want to keep it on the query level, you could use this principle inside two separate subqueries (one for future, one for past dates) [inside a union](https://stackoverflow.com/questions/24683766/how-to-use-order-by-inside-union). – El_Vanja May 08 '21 at 17:29

0 Answers0