CODEWITHSUNDEEP
mysqlsqldatabasedate
sidyll
sidyll

Reputation: 59287

Select dates from the future or past, if future is not available

I have a simple table for events with a date column. I can easily select the next n events with (assuming n = 3):

SELECT * FROM events WHERE `date` > NOW() ORDER BY `date` LIMIT 3

However, not aways there will be 3 events in the future. In this case, I'd like to return the ones available in the future and complete what is missing with the closest ones to today. E.g., if today is day 12-04, the following dates marked with a * should be selected of the whole list:

10-03
20-03
30-03 *
10-04 *
20-04 *

While I can easily check the result of the first query to find out how many rows were returned and build another query to find the past dates if necessary, I'm interested to know if there is a way to fetch these rows in a single query.

Upvotes: 0

Views: 253

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You can use multiple keys in the order by. So:

SELECT e.*
FROM events
ORDER BY (date > now()) DESC,  -- future events first
         (CASE WHEN date > now() THEN date END) ASC -- nearest future events first
         date DESC  -- other dates in descending order
LIMIT 3;

If your table is large, it is probably faster to get three events from the near future and near past and combine those:

select e.*
from ((select e.*
       from events e
       where date > now()
       order by date asc
       limit 3
      ) union all
      (select e.*
       from events e
       where date <= now()
       order by date desc
       limit 3
      )
     ) e
order by date desc
limit 3;

Upvotes: 2

Related Questions