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:

If I order by event title and event date I get the following:

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:

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.

CodePudding user response：

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:

Check the demo here.