MySQL-condition on partition size

table1 has 3 columns in my database: id, category, timestamp. I need to query the newest 3 rows from each category:

WITH ranked_rows AS
(SELECT t.*, ROW_NUMBER() OVER (PARTITION BY category ORDER BY t.timestamp DESC) AS rn
FROM table1 AS t)
SELECT ranked_rows.* FROM ranked_rows WHERE rn<=3

now I need to add one more condition: select only from the partitions which have at least 3 rows. how to add this condition?

Answer

here is another way:

select * from (
   SELECT t.*
      , ROW_NUMBER() OVER (PARTITION BY category ORDER BY t.timestamp DESC) AS rn
      , count(*) OVER (PARTITION BY category) AS cnt
FROM table1 AS t
) t 
WHERE rn<=3 and cnt>= 3