CODEWITHSUNDEEP
mysqlsqldatetime
Or Weinberger
Or Weinberger

Reputation: 7472

SQL Query to show nearest date?

I'm trying to figure out how to write a MySQL query that will return the closest 3 events in terms of date.

This is my table:

EVENT_ID    EVENT_NAME     EVENT_START_DATE(DATETIME)
1           test           2011-06-01 23:00:00
2           test2          2011-06-03 23:00:00
3           test3          2011-07-01 23:00:00
4           test4          2011-08-09 23:00:00
5           test5          2011-06-02 23:00:00
6           test6          2011-04-20 23:00:00

So the query result should be for ID's 1,2,5 as they are the closest to occur in comparison to the current date..

EDIT: query should find only future events.

Upvotes: 43

Views: 71013

Answers (5)

Damjan Pavlica
Damjan Pavlica

Reputation: 33943

The query from accepted answer actually just sort previously selected values, not filter them before select. But this query works for me:

SELECT event_id, event_date
FROM events 
WHERE ABS(TIMESTAMPDIFF(DAY, event_date, $some_date)) < 10
ORDER BY event_date

Explanation: number 10 is a day range (both after and before). Without ABS() you can select only previous or future events, but I needed the closest.

Upvotes: 2

Mat
Mat

Reputation: 6715

SELECT event_id 
FROM Table 
ORDER BY ABS( DATEDIFF( EVENT_START_DATE, NOW() ) ) 
LIMIT 3

The ABS() means that an event 1 day ago is just as close as an event 1 day in the future. If you only want events that haven't happened yet, do

SELECT event_id 
FROM Table 
WHERE EVENT_START_DATE > NOW() 
ORDER BY EVENT_START_DATE 
LIMIT 3

Upvotes: 96

maxtwoknight
maxtwoknight

Reputation: 5346

SELECT event_id FROM Table ORDER BY EVENT_START_DATE LIMIT 3

Upvotes: -1

Andre Backlund
Andre Backlund

Reputation: 6943

I suppose this is what you'd be looking for. It's similar to everyone elses responses aswell.

SELECT EVENT_ID FROM TABLE WHERE EVENT_START_DATE > NOW() ORDER BY ABS(DATEDIFF(EVENT_START_DATE, NOW())) ASC LIMIT 3

Upvotes: 0

hsz
hsz

Reputation: 152206

  SELECT *
    FROM table
   WHERE EVENT_START_DATE >= NOW()
ORDER BY EVENT_START_DATE
   LIMIT 3

Upvotes: 5

Related Questions