I have the following query:
SELECT el.event_title
,se.event_eventDateAndTime
FROM events__events_list el
JOIN events__sub_events se ON el.UID = se.event_masterEvent
WHERE (se.event_eventDateAndTime BETWEEN CURDATE() AND (CURDATE() + INTERVAL 45 DAY))
ORDER BY se.event_eventDateAndTime ASC;
This produces a list of events ordered by the date:
| event_title | event_eventDateAndTime |
|---|---|
| Event 2 | 2023-02-22 19:30:00 |
| Event 1 | 2023-02-23 19:30:00 |
| Event 2 | 2023-02-24 19:30:00 |
| Event 3 | 2023-02-25 19:30:00 |
| Event 2 | 2023-02-26 19:30:00 |
| Event 1 | 2023-02-27 19:30:00 |
If I order by event title and event date I get the following:
| event_title | event_eventDateAndTime |
|---|---|
| Event 1 | 2023-02-23 19:30:00 |
| Event 1 | 2023-02-27 19:30:00 |
| Event 2 | 2023-02-22 19:30:00 |
| Event 2 | 2023-02-24 19:30:00 |
| Event 2 | 2023-02-26 19:30:00 |
| Event 3 | 2023-02-25 19:30:00 |
But I want to order by the event title and the date, so the events are kept together and then ordered by the date of the first instance... like this:
| event_title | event_eventDateAndTime |
|---|---|
| Event 2 | 2023-02-22 19:30:00 |
| Event 2 | 2023-02-24 19:30:00 |
| Event 2 | 2023-02-26 19:30:00 |
| Event 1 | 2023-02-23 19:30:00 |
| Event 1 | 2023-02-27 19:30:00 |
| Event 3 | 2023-02-25 19:30:00 |
I'm lost and I'm not sure how best to move forward. I've tried searching but I can't find anything that matches my problem.
You can use the MIN window function that selects the minimum "event_eventDateAndTime" for each "event_title" for you, in the ORDER BY clause.
SELECT el.event_title
,se.event_eventDateAndTime
FROM events__events_list el
INNER JOIN events__sub_events se ON el.UID = se.event_masterEvent
WHERE se.event_eventDateAndTime BETWEEN CURDATE() AND (CURDATE() + INTERVAL 45 DAY)
ORDER BY MIN(event_eventDateAndTime) OVER(PARTITION BY event_title),
event_title,
event_eventDateAndTime
Output:
| event_title | event_eventDateAndTime |
|---|---|
| Event 2 | 2023-02-22 19:30:00 |
| Event 2 | 2023-02-24 19:30:00 |
| Event 2 | 2023-02-26 19:30:00 |
| Event 1 | 2023-02-23 19:30:00 |
| Event 1 | 2023-02-27 19:30:00 |
| Event 3 | 2023-02-25 19:30:00 |
Check the demo here.