title: Get most recent groups ordered by users last login date DESC
body: <p>The title makes it sound easy but what I'd like to do is get the last 20 groups from the <code>groups</code> table, ordered by their corresponding users last login date. A group can have one or more <code>users</code> and ultimately what I want to do is find out which groups have had the least user activity and retrieve the last login date.</p>
  
  <p>Here's the query I came up with - </p>
  
  <pre><code>SELECT DISTINCT g.name, user_max.max_login_last_at FROM groups g
  
  LEFT JOIN group_user gu on g.id = gu.group_id
  LEFT JOIN (
      SELECT MAX(login_last_at) max_login_last_at, u.id
      FROM users u GROUP BY id
  ) AS user_max ON (user_max.id = gu.user_id)
  
  ORDER BY user_max.max_login_last_at ASC
  </code></pre>
  
  <p>The problem is when I perform the join, it pulls in every <code>group_user</code> record and results in duplicates. I feel like there could be an easy solution to this one but I can't seem to figure it out!</p>
  
  <p><strong><code>groups</code> table</strong></p>
  
  <pre><code>id  name
  1   Group 1
  2   Group 2
  </code></pre>
  
  <p><strong><code>users</code> table</strong></p>
  
  <pre><code>id  email   login_last_at
  1   user1@example.com   2018-10-17 16:08:47
  2   user2@example.com   2018-10-02 15:41:53
  3   user3@example.com   NULL
  4   user4@example.com   2018-10-08 12:01:48
  5   user5@example.com   2018-10-15 9:24:57
  6   user6@example.com   2018-10-17 11:10:58
  7   user7@example.com   2018-10-17 15:33:03
  </code></pre>
  
  <p><strong><code>group_user</code> table</strong></p>
  
  <pre><code>id  group_id    user_id
  1   1   1
  2   2   1
  3   1   2
  4   1   3
  5   1   4
  6   2   5
  7   1   5
  8   2   6
  9   1   7
  </code></pre>
  
  <p>Current example result - </p>
  
  <pre><code>name    max_login_last_ts
  Group 1 2018-10-02 15:41:53
  Group 1 2018-10-08 12:01:48
  Group 2 2018-10-15 09:24:57
  Group 1 2018-10-15 09:24:57
  Group 2 2018-10-17 11:10:58
  Group 1 2018-10-17 15:33:03
  Group 1 2018-10-17 16:08:47
  Group 2 2018-10-17 16:08:47
  Group 1 2018-10-18 08:55:17
  </code></pre>
  
  <p>The problem is as you can see in the result above is that I'm getting all groups, all I really want is the following - </p>
  
  <pre><code>name      max_login_last_ts
  Group 2   2018-10-17 16:08:47
  Group 1   2018-10-18 08:55:17
  </code></pre>
  
  <p>Thanks in advance!</p>