Here’s a fairly portable query to do what you want:
SELECT *
FROM table1 a
WHERE a."ROWID" IN (
SELECT b."ROWID"
FROM table1 b
WHERE b."Score" >= 20
AND b."ROWID" IS NOT NULL
AND a."CID" = b."CID"
ORDER BY b."CID", b."SortKey"
LIMIT 2
)
ORDER BY a."CID", a."SortKey";
The query uses a correlated subquery with a sort and limit to produce a list of ROWID
s that should appear in the final result. Because the correlated subquery is executed for every row, whether or not it’s included in the result, it may not be as efficient as the window function version given below – but unlike that version it’ll work on SQLite3, which doesn’t support window functions.
This query requires that ROWID
is unique (can be used as a primary key).
I tested the above in PostgreSQL 9.2 and in SQLite3 3.7.11 ; it works fine in both. It won’t work on MySQL 5.5 or the latest 5.6 milestone because MySQL doesn’t support LIMIT
in a subquery used with IN
.
SQLFiddle demos:
-
PostgreSQL (works fine): http://sqlfiddle.com/#!12/22829/3
-
SQLite3 (works fine, same query text, but needed single-valued inserts due to apparent JDBC driver limitation): http://sqlfiddle.com/#!7/9ecd8/1
-
MySQL 5.5 (fails two ways; MySQL doesn’t like
a."ROWID"
quoting even inANSI
mode so I had to un-quote; then it fails withThis version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery
): http://sqlfiddle.com/#!2/e1f31/2
SQLite demo showing it works just fine on the SQLite3 command line: http://pastebin.com/26n4NiUC
Output (PostgreSQL):
ROWID | CID | PID | Score | SortKey
-------+-----+-----+-------+---------
2 | C1 | P2 | 20 | 2
3 | C1 | P3 | 30 | 3
5 | C2 | P5 | 30 | 2
4 | C2 | P4 | 20 | 3
7 | C3 | P7 | 20 | 2
(5 rows)
If you want to filter for a particular CID
, just add AND "CID" = 'C1'
or whatever to the outer WHERE
clause.
Here’s a closely related answer with more detailed examples: https://stackoverflow.com/a/13411138/398670
Since this was originally tagged just SQL
(no SQLite)… just for completeness, in PostgreSQL or other DBs with SQL-standard window function support I’d probably do this:
SELECT "ROWID", "CID", "PID", "Score", "SortKey"
FROM (
SELECT *, row_number() OVER (PARTITION BY "CID" ORDER BY "SortKey") AS n
FROM table1
WHERE "Score" >= 20
) x
WHERE n < 3
ORDER BY "CID", "SortKey";
which produces the same result. SQLFiddle, including extra C1
row to demonstrate that the limiting filter actually works: http://sqlfiddle.com/#!12/22829/1
If you want to filter for a particular CID
, just add AND "CID" = 'C1'
or whatever to the inner WHERE
clause.
BTW, your test data is insufficient, since it can never have more than two rows for any CID with score > 20 anyway.