table1 has 3 columns in my database: id, category, timestamp. I need to query the newest 3 rows from each category: 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:

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

​x
 
WITH ranked_rows AS(SELECT t.*, ROW_NUMBER() OVER (PARTITION BY category ORDER BY t.timestamp DESC) AS rnFROM 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

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

Advertisement

Answer