title: Postgres group by columns and within group select other columns by max aggregate
body: <p>This is probably a standard problem, and I've keyed off some other <a href="/questions/tagged/greatest-n-per-group" class="post-tag" title="show questions tagged &#39;greatest-n-per-group&#39;" rel="tag">greatest-n-per-group</a> answers, but so far been unable to resolve my current problem.</p>
  
  <pre><code>A              B             C
  +----+-------+ +----+------+ +----+------+-------+
  | id | start | | id | a_id | | id | b_id | name  |
  +----+-------+ +----+------+ +----+------+-------+
  |  1 |     1 | |  1 |    1 | |  1 |    1 | aname |
  |  2 |     2 | |  2 |    1 | |  2 |    2 | aname |
  +----+-------+ |  3 |    2 | |  3 |    3 | aname |
                 +----+------+ |  4 |    3 | bname |
                               +----+------+-------+
  </code></pre>
  
  <p>In English what I'd like to accomplish is:</p>
  
  <ol>
  <li>For each c.name, select its newest entry based on the start time in a.start</li>
  </ol>
  
  <p>The SQL I've tried is the following:</p>
  
  <pre><code>SELECT a.id, a.start, c.id, c.name 
  FROM a
  INNER JOIN (
      SELECT id, MAX(start) as start
      FROM a
      GROUP BY id
  ) a2 ON a.id = a2.id AND a.start = a2.start
  JOIN b
  ON a.id = b.a_id
  JOIN c
  on b.id = c.b_id
  GROUP BY c.name;
  </code></pre>
  
  <p>It fails with errors such as:</p>
  
  <pre><code>ERROR: column "a.id" must appear in the GROUP BY clause or be used in an aggregate function Position: 8
  </code></pre>
  
  <p>To be useful I really need the ids from the query, but cannot group on them since they are unique.  Here is an example of output I'd love for the first case above:</p>
  
  <pre><code>+------+---------+------+--------+
  | a.id | a.start | c.id | c.name |
  +------+---------+------+--------+
  |    2 |       2 |    3 | aname  |
  |    2 |       2 |    4 | bname  |
  +------+---------+------+--------+
  </code></pre>
  
  <p>Here is a <a href="http://sqlfiddle.com/#!15/c9dc3/2" rel="nofollow">Sqlfiddle</a></p>
  
  <p>Edit - removed second case</p>