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.