0

Groupwise maximum queries are clearly harder than one would expect.

I've found there are a number of approaches to this, but all that I've found seem to be based on a single table, and I've not been able to work out how it should work when based on multiple joined tables.

Say I have users and events

+--------+------+
| UserId | Name |
+--------+------+
| 100001 | Amy  |
| 100002 | Bob  |
| 100003 | Zoe  |
+--------+------+

+---------+---------+------------+
| EventId | Event   | Date       |
+---------+---------+------------+
|  100001 | Event1  | 2021-01-01 |
|  100002 | Event2  | 2021-01-02 |
+---------+---------+------------

and an attendance table to implement a many:many relationship between them

+--------+---------+
| UserId | EventId |
+--------+---------+
| 100001 |  100001 |
| 100001 |  100002 |
| 100003 |  100001 |
+--------+---------+

If I have a query to select attendance at events

SELECT User.Name, Event.Event, Event.Date
FROM User
LEFT JOIN Attendance USING (UserId)
LEFT JOIN Event USING (EventId)

I will get

+------+--------+------------+
| Name | Event  | Date       |
+------+--------+------------+
| Amy  | Event1 | 2021-01-01 |
| Amy  | Event2 | 2021-01-02 |
| Bob  | NULL   | NULL       |
| Zoe  | Event1 | 2021-01-01 |
+------+--------+------------+

How could I get a list of only the last events attended by each user? i.e.

+------+--------+------------+
| Name | Event  | Date       |
+------+--------+------------+
| Amy  | Event2 | 2021-01-02 |
| Bob  | NULL   | NULL       |
| Zoe  | Event1 | 2021-01-01 |
+------+--------+------------+

I can't wrap my head around how to find the correct event id for the event with the maximum date, and join that back to the user.

Could anyone help?

ChrisV
  • 8,748
  • 3
  • 48
  • 38
  • Using `group by name` and `max(date)` doesn't work? Be careful date is a sql keyword reserved – DonKnacki May 28 '21 at 15:52
  • @DonKnacki Date is a keyword but not a reserved keyword. See https://dev.mysql.com/doc/refman/8.0/en/keywords.html#keywords-8-0-detailed-D, there is no `(R)` following the date keyword. – Bill Karwin May 28 '21 at 15:57
  • @DonKnacki `SELECT UserId, max(Date) FROM Attendance INNER JOIN Event USING (EventId) GROUP BY UserId` will return a random UserId, not the one associated with the maximum date – ChrisV May 28 '21 at 16:04
  • Note this is not a duplicate of https://stackoverflow.com/questions/33145885, due to the intersection table Attendance between User and Event – ChrisV May 28 '21 at 17:19

0 Answers0