sql query - how to apply limit within group by
- by Raj
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
