MySQL - multiple CASE ordering with joined table

Question

I have two tables:

EVENTS

ID	Name
1	First event
2	Second event
3	Third event
4	Fourth event
5	Fifth event

EVENTS_META

ID	EventID	MetaKey	MetaValue
1	2	date_expired	2023年02月09日 00:00:00
2	2	date_expired	2023年01月23日 00:00:00
3	3	date_expired	2023年04月30日 00:00:00
4	3	date_expired	2023年01月22日 00:00:00
5	4	date	2023年06月12日 00:00:00
6	4	date	2023年02月03日 00:00:00
7	5	date_expired	2023年02月13日 00:00:00
8	5	date	2023年01月02日 00:00:00
9	5	date	2023年01月01日 00:00:00

I would like to select all items from events table ordered by the following logic:

1. First "group" of items on the list are the ones that have any "date" rows in events_meta table.
2. Second "group" of items on the list are the ones that have any "date_expired" rows in events_meta table.
3. Third "group" of items on the list are the ones that don't have "date" nor "date_expired" rows.

Also, within each of those groups (except the last one) events are ordered by their "date" or "date_expired" minimum date values. So the first group is ordered by "date" dates. Second group is ordered by "date_expired" dates.

Each event can have any number of "date" or "date_expired" rows attached to them.

The desired outcome would be:

1. Fifth event
2. Fourth event
3. Third event
4. Second event
5. First event

I think I have managed to create the "groups" but I'm struggling to sort the groups by dates:

SELECT events.Name FROM `events` LEFT JOIN `events_meta` ON events.ID = events_meta.EventId 
GROUP BY events.Name
ORDER BY 
 CASE WHEN (SELECT COUNT(*) FROM events_meta WHERE events.ID = events_meta.EventID AND events_meta.MetaKey = 'date') > 0 THEN 0
 WHEN (SELECT COUNT(*) FROM events_meta WHERE events.ID = events_meta.EventID AND events_meta.MetaKey = 'date_expired' AND events_meta.MetaKey != 'date') > 0 THEN 1
 ELSE 2
 END;

Here's a dbfiddle with the exact same dataset: https://dbfiddle.uk/55vp__vZ

Answer 1

SELECT events.Name
FROM events
LEFT JOIN events_meta ON events.ID = events_meta.EventId
GROUP BY 1
ORDER BY MIN(CASE WHEN events_meta.MetaKey = 'date'
 THEN events_meta.MetaValue 
 ELSE '9999-12-31'
 END),
 MIN(CASE WHEN events_meta.MetaKey = 'date_expired' 
 THEN events_meta.MetaValue 
 ELSE '9999-12-31' 
 END)

fiddle.

• Martin J: Thanks, this is exactly what I was looking for. Appreaciate the breakdown in the fiddle as well, much easier to understand.