title: PostgreSQL MAX and GROUP BY
body: <p>I have a table with <code>id</code>, <code>year</code> and <code>count</code>. </p>
  
  <p>I want to get the <code>MAX(count)</code> for each <code>id</code> and keep the <code>year</code> when it happens, so I make this query:</p>
  
  <pre><code>SELECT id, year, MAX(count)
  FROM table
  GROUP BY id;
  </code></pre>
  
  <p>Unfortunately, it gives me an error: </p>
  
  <blockquote>
    <p>ERROR:  column "table.year" must appear in the GROUP BY clause or be
    used in an aggregate function</p>
  </blockquote>
  
  <p>So I try:</p>
  
  <pre><code>SELECT id, year, MAX(count)
  FROM table
  GROUP BY id, year;
  </code></pre>
  
  <p>But then, it doesn't do <code>MAX(count)</code>, it just shows the table as it is. I suppose because when grouping by <code>year</code> and <code>id</code>, it gets the max for the <code>id</code> of that specific year.</p>
  
  <p>So, how can I write that query? I want to get the <code>id</code>´s <code>MAX(count)</code> and the year when that happens.</p>