title: Performing a within sub-group aggregate calculation
body: <p>I want to calculate 'within group quotas<code>using the bottom</code>N` values. I've read through several examples <a href="http://www.artfulsoftware.com/infotree/queries.php#101" rel="nofollow">here</a> which explains a lot of different ways of calculating this measure, but I don't fully understand them (and some are not specific to Postgresql). I'm using the following table to illustrate my question:</p>
  
  <pre><code>    a | b | x
     ---|---|------
      1 | 1 | 10.00
      1 | 2 | 15.00
      1 | 1 | 10.00
      1 | 2 | 15.00
      2 | 2 | 20.00
      2 | 1 | 21.00
      2 | 2 | 18.00
  </code></pre>
  
  <p><code>SQL</code> to create the table:</p>
  
  <pre><code>CREATE TABLE test(a int,b int,x decimal(6,2));
  INSERT INTO test VALUES(1,1,10),(1,2,15), (1,1,10), (1,2,15), (2,2,20),(2,1,21),(2,2,18);
  </code></pre>
  
  <p>I want to be able to calculate the minimum of the lowest <code>N</code> values, per group. In this example, I am letting <code>N = 2</code>. The first step that I have tried is the following:</p>
  
  <pre><code>SELECT 
    t1.a,  
    AVG(t1.x) as avg_x
  FROM 
    test AS t1
  GROUP BY t1.a
  ORDER BY avg_x
  </code></pre>
  
  <p>which returns:</p>
  
  <pre><code>a | avg_x
  --|------
  1 | 12.50
  2 | 19.66
  </code></pre>
  
  <p>What I've tried doing (which give the incorrect result) is to make a subquery, and to limit the number of results:</p>
  
  <pre><code>SELECT foo.* FROM 
  (SELECT 
    t1.a,  
    AVG(t1.x) as avg_x
  FROM 
    test AS t1
  GROUP BY t1.a
  ORDER BY avg_x
  ) as foo
  ORDER BY foo.avg_x
  LIMIT 2
  </code></pre>
  
  <p>I know this is not correct, as it is not using the <code>LIMIT</code> per sub-group. To clarify the table that I want returned is:</p>
  
  <pre><code>a | avg_x
  --|------
  1 | 10.00
  2 | 19.00
  </code></pre>
  
  <p>The raw result for <code>a=1</code> is <code>x = 10.0, 10.0, 21</code>, with average of the values <code>10.0, 10.0</code>.</p>