how do i get the correct id with the query results

Question

I want to create a stored procedure in MySQL, but first, I want to get the query right. However, I keep getting the problem that I can't seem to get the correct id back from my query that correspond with the DateTime stamps that I get back.

this is the table I am trying to get the result from:

id  EventId   start                 end
1   1         2019-04-05 00:00:00   2019-04-07 00:00:00
2   2         2020-04-03 00:00:00   2020-04-03 00:00:00
3   3         2020-04-02 00:00:00   2020-04-02 00:00:00
7   1         2020-06-11 00:00:00   2020-06-11 00:00:00
9   2         2020-06-18 00:00:00   2020-06-18 00:00:00
10  3         2020-06-11 00:00:00   2020-06-11 00:00:00
11  3         2020-06-07 00:00:00   2020-06-07 00:00:00

query:

SELECT DISTINCT Eventid, MIN(start), id
from date_planning
WHERE `start` >= NOW()
GROUP BY Eventid

this gives me the following result

EventId   Min(start)            id
1         2020-06-11 00:00:00   3
2         2020-06-18 00:00:00   9
3         2020-06-07 00:00:00   10

but these are the correct ids that belong to those DateTimes:

EventId   Min(start)            id
1         2020-06-11 00:00:00   7
2         2020-06-18 00:00:00   9
3         2020-06-07 00:00:00   11

Answer 1

You want the row with the minimum "future" date for each eventId. To solve this greatest-n-per-group problem, you need to filter rather than aggregate. Here is one option using a correlated subquery:

select dt.*
from date_planning dt
where dt.start = (
    select min(dt1.start)
    from date_planning dt1
    where dt1.eventId = dt.eventId and dt1.start >= now()
)

For performance, you need an index on (eventId, start).