title: SQL groupwise maximum query with multiple tables
body: <p>Groupwise maximum queries are clearly harder than one would expect.</p>
  <p>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.</p>
  <p>Say I have users and events</p>
  <pre><code>+--------+------+
  | UserId | Name |
  +--------+------+
  | 100001 | Amy  |
  | 100002 | Bob  |
  | 100003 | Zoe  |
  +--------+------+
  
  +---------+---------+------------+
  | EventId | Event   | Date       |
  +---------+---------+------------+
  |  100001 | Event1  | 2021-01-01 |
  |  100002 | Event2  | 2021-01-02 |
  +---------+---------+------------
  </code></pre>
  <p>and an attendance table to implement a many:many relationship between them</p>
  <pre><code>+--------+---------+
  | UserId | EventId |
  +--------+---------+
  | 100001 |  100001 |
  | 100001 |  100002 |
  | 100003 |  100001 |
  +--------+---------+
  </code></pre>
  <p>If I have a query to select attendance at events</p>
  <pre><code>SELECT User.Name, Event.Event, Event.Date
  FROM User
  LEFT JOIN Attendance USING (UserId)
  LEFT JOIN Event USING (EventId)
  </code></pre>
  <p>I will get</p>
  <pre><code>+------+--------+------------+
  | Name | Event  | Date       |
  +------+--------+------------+
  | Amy  | Event1 | 2021-01-01 |
  | Amy  | Event2 | 2021-01-02 |
  | Bob  | NULL   | NULL       |
  | Zoe  | Event1 | 2021-01-01 |
  +------+--------+------------+
  </code></pre>
  <p>How could I get a list of only the last events attended by each user? i.e.</p>
  <pre><code>+------+--------+------------+
  | Name | Event  | Date       |
  +------+--------+------------+
  | Amy  | Event2 | 2021-01-02 |
  | Bob  | NULL   | NULL       |
  | Zoe  | Event1 | 2021-01-01 |
  +------+--------+------------+
  </code></pre>
  <p>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.</p>
  <p>Could anyone help?</p>