title: Return row with the max value of one column per group
body: <p>I am having a hard time doing this without searching the same table at least twice in order to grab the max row, and then grab the value for that row. The table in question is quite big so this is unacceptable.</p>
  
  <p>Here is what my table might look like:</p>
  
  <pre><code>SCORES
  ID    ROUND    SCORE
  1     1        3
  1     2        6
  1     3        2
  2     1        10
  2     2        12
  3     1        6
  </code></pre>
  
  <p>I need to return the score that each ID got in the most recent round. That is, the row with the max (round), but not the max score.</p>
  
  <pre><code>OUTPUT:
  ID   ROUND   SCORE
  1    3       2
  2    2       12
  3    1       6
  </code></pre>
  
  <p>Right now I have:</p>
  
  <pre><code>SELECT * FROM 
  (SELECT id, round,
  CASE WHEN (MAX(round) OVER (PARTITION BY id)) = round THEN score ELSE NULL END score
   FROM
   SCORES
   where id in (1,2,3)
  ) scorevals
  WHERE
  scorevals.round is not null;
  </code></pre>
  
  <p>This works, but is pretty inefficient (I have to manually filter out all of these rows, when I should just be able to not grab those rows in the first place.)</p>
  
  <p>What can I do to get the right values?</p>