sql query - how to apply limit within group by

Question

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

Answer 1

In MySQL:

SELECT  l.*
FROM    (
        SELECT  cid,
                COALESCE(
                (
                SELECT  id
                FROM    mytable li
                WHERE   li.cid = dlo.cid
                        AND li.score >= 20
                ORDER BY
                        li.cid, li.id
                LIMIT 1, 1
                ), CAST(0xFFFFFFFF AS DECIMAL)) AS mid
        FROM    (
                SELECT  DISTINCT cid
                FROM    mytable dl
                ) dlo
        ) lo, mytable l
WHERE   l.cid >= lo.cid
        AND l.cid <= lo.cid
        AND l.id <= lo.mid
        AND l.score >= 20

Answer 2

Shouldn't it be something as simple as **

SELECT CID, PID, Score, SortKey
FROM T1
WHERE score >= 20
ORDER BY CID, Sortkey

**

or am I missing something?