SQLITE select by group and limit results for each group

user_2094560 0
Viewed 122

I have the following SQLITE table:

COL1 COL2 COL3
tit1 cat1 time1
tit2 cat3 time2
tit3 cat1 time3
tit4 cat1 time4
tit5 cat3 time5
tit6 cat2 time6
tit7 cat1 time7

I want to SELECT all rows grouped by COL2, WHERE COL2 == cat1 or cat2, but LIMIT the results to max 2 rows for each group and ORDER the results by COL3 DESC.

So, for instance, in this case I want to get only:

COL1 COL2 COL3
tit7 cat1 time7
tit4 cat1 time4
tit6 cat2 time6

Is it possible to obtain this with (possibly) one or max 2 queries?

So far I tried this, but it doesn't work as expected:

SELECT * FROM "tablename" WHERE "COL2" = "cat1" OR "COL2" = "cat2" GROUP BY "COL2" ORDER BY "COL3" DESC LIMIT 2

Thank you for your help

sqlite php
1 Answers

Filter the table and use ROW_NUMBER() window function:

SELECT COL1, COL2, COL3
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY COL2 ORDER BY COL3 DESC) rn 
  FROM tablename  
)
WHERE rn <= 2
ORDER BY COL2, COL3 DESC
user_104988280
Related