title: sql query - how to apply limit within group by
body: <p>I have a table named t1 with following fields: ROWID, CID, PID, Score, SortKey</p>
  
  <p>it has the following data:</p>
  
  <pre><code>1, C1, P1, 10, 1
  2, C1, P2, 20, 2
  3, C1, P3, 30, 3
  
  4, C2, P4, 20, 3
  5, C2, P5, 30, 2
  
  6, C3, P6, 10, 1
  7, C3, P7, 20, 2
  </code></pre>
  
  <p>what query do I write so that it applies group by on CID, but instead of returning me 1 single result per group, it returns me a max of 2 results per group. also where condition is score >= 20 and I want the results ordered by CID and SortKey.</p>
  
  <p>If I had to run my query on above data, I would expect the following result:</p>
  
  <p><strong>RESULTS FOR C1 - note: ROWID 1 is not considered as its score &lt; 20</strong></p>
  
  <pre><code>C1, P2, 20, 2
  C1, P3, 30, 3
  </code></pre>
  
  <p><strong>RESULTS FOR C2 - note: ROWID 5 appears before ROWID 4 as ROWID 5 has lesser value 
  SortKey</strong></p>
  
  <pre><code>C2, P5, 30, 2
  C2, P4, 20, 3
  </code></pre>
  
  <p><strong>RESULTS FOR C3 - note: ROWID 6 does not appear as its score is less than 20 so only 1 record returned here</strong></p>
  
  <pre><code>C3, P7, 20, 2
  </code></pre>
  
  <p>IN SHORT, I WANT A LIMIT WITHIN A GROUP BY. I want the simplest solution and want to avoid temp tables. sub queries are fine. Also note I am using SQLite for this.</p>