title: MySql multiple GROUP BY alternative
body: <p>I have two tables: <code>teams</code> and <code>messages</code></p>
  
  <pre><code>teams
  ========
  id | name
  
  
  messages
  ============================
  id | team_id | created_date
  </code></pre>
  
  <p>I need a result set that looks like this:</p>
  
  <pre><code>team name  |  number of messages  | most recent message
  </code></pre>
  
  <p>For example:</p>
  
  <pre><code>Joe Blow's Group   |   23   |   2018-08-29
  </code></pre>
  
  <p>My initial try at this:</p>
  
  <pre><code>select team.id, team.name, count(messages.team_id) as 'Number of 
  Messages', messages.created_date as 'Last Message' from messages inner join 
  teams on messages.team_id = team.id group by team.name
  </code></pre>
  
  <p>Produced:</p>
  
  <pre><code>Joe Blow's Team   |   23   |   2018-02-14
  Bob's Team   |   12   |   2018-04-15
  </code></pre>
  
  <p>etc etc</p>
  
  <p>The issue is that the dates are of the oldest message created for that team, not the most recent. If I try to add <code>sort by</code> it only sorts this result set but the wrong dates. If I try multiple 'group by' I get a row for every message.</p>
  
  <p>Does this need a subquery or is there a simpler way to have the <code>created_date</code> be the most recent?</p>