SQL query by dates?

Question

SQL

Forming a query
SELECT event_id, date, case
when date(date) = curdate() then '1'
when date(date) > curdate() then '2'
else '3' end as events_group FROM relationship_events_date ORDER BY events_group ASC
I get
3 | 2019-12-04 00:00:00 | 1
2 | 2019-12-04 00:00:00 | 1
5 | 2019-12-04 00:00:00 | 1
4 | 2019-12-14 00:00:00 | 2
4 | 2019-12-18 00:00:00 | 2
5 | 2019-12-25 00:00:00 | 2
3 | 2019-12-01 00:00:00 | 3
1 | 2019-11-20 00:00:00 | 3
1 | 2019-11-28 00:00:00 | 3
1 | 2019-12-03 00:00:00 | 3
When grouping
SELECT event_id, date, case
when date(date) = curdate() then '1'
when date(date) > curdate() then '2'
else '3' end as events_group FROM relationship_events_date GROUP BY event_id ORDER BY events_group ASC
Get
2 | 2019-12-04 00:00:00 | 1
5 | 2019-12-04 00:00:00 | 1
4 | 2019-12-14 00:00:00 | 2
3 | 2019-12-01 00:00:00 | 3
1 | 2019-11-20 00:00:00 | 3
But here's the thing, event_id should refer to events_group 1 and it falls into 3, since the last event_id is taken during grouping Please
advise what you can think of

Answer 1

According to the GROUP BY standard, the selection can only include fields that are grouped or aggregate functions from other fields. MySQL allows selection of other fields, but does not guarantee that they will be taken from any particular row.
You are grouping by event_id, and before grouping you have two rows with event_id = 3. Since the date and events_group fields are not grouped and the aggregate function is not taken on them, they can be taken from any of these two rows.
Formally, your request looks like

SELECT event_id, ANY_VALUE(date), ANY_VALUE(case ... as events_group)
  FROM relationship_events_date
  GROUP BY event_id
  ORDER BY events_group ASC