hey guys assuming i have a table named t1 with following fields: ROWID, CID, PID, Score, SortKey

it has the following data:

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

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.

If I had to run my query on above data, i would expect the following result:

RESULTS FOR C1 - note: ROWID 1 is not considered as its score < 20

C1, P2, 20, 2
C1, P3, 30, 3

RESULTS FOR C2 - note: ROWID 5 appears before ROWID 4 as ROWID 5 has lesser value SortKey

C2, P5, 30, 2
C2, P4, 20, 3

RESULTS FOR C3 - note: ROWID 6 does not appear as its score is less than 20 so only 1 record returned here

C3, P7, 20, 2

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