title: Using LIMIT within GROUP BY to get N results per group?
body: <p>The following query:</p>
  
  <pre><code>SELECT
  year, id, rate
  FROM h
  WHERE year BETWEEN 2000 AND 2009
  AND id IN (SELECT rid FROM table2)
  GROUP BY id, year
  ORDER BY id, rate DESC
  </code></pre>
  
  <p>yields:</p>
  
  <pre><code>year    id  rate
  2006    p01 8
  2003    p01 7.4
  2008    p01 6.8
  2001    p01 5.9
  2007    p01 5.3
  2009    p01 4.4
  2002    p01 3.9
  2004    p01 3.5
  2005    p01 2.1
  2000    p01 0.8
  2001    p02 12.5
  2004    p02 12.4
  2002    p02 12.2
  2003    p02 10.3
  2000    p02 8.7
  2006    p02 4.6
  2007    p02 3.3
  </code></pre>
  
  <p>What I'd like is only the top 5 results for each id:</p>
  
  <pre><code>2006    p01 8
  2003    p01 7.4
  2008    p01 6.8
  2001    p01 5.9
  2007    p01 5.3
  2001    p02 12.5
  2004    p02 12.4
  2002    p02 12.2
  2003    p02 10.3
  2000    p02 8.7
  </code></pre>
  
  <p>Is there a way to do this using some kind of LIMIT like modifier that works within the GROUP BY?</p>