Mysql - Select the most inserted records in a given period

Question

I am needing to select all most recurring records (top 5) within a time interval but am not getting.

Basically when I do this:

SELECT assunto, count(*) AS total 
 FROM tabMsgs 
WHERE dtCriacao > '2021-05-08' AND dtCriacao < '2021-05-16' GROUP BY assunto 
ORDER BY total DESC LIMIT 5;

I get the count of records, grouped by subject in descending order limited to 5... Great, but now that I know who are the top 5 subjects with more count of records and their respective amount I want to select all these records.

I’m doing so unsuccessfully:

SELECT id, assunto, dtCriacao 
  FROM tabMsgs A 
 WHERE dtCriacao > '2021-05-08' 
   AND dtCriacao < '2021-05-16' 
   AND assunto = (SELECT max(assunto) from tabMsgs B WHERE B.assunto = A.assunto);

This query is returning all records in this time interval, but I need you to return all records of the 5 subjects with the highest amount of records.

I don’t know if I’ve made myself clear.

Answer 1

You can use a INNER JOIN with that sub-query, something like that:

SELECT
    a.id, a.assunto, a.dtCriacao
FROM
    tabMsgs
INNER JOIN (
    SELECT
        assunto, count(*) AS total
    FROM
        tabMsgs
    WHERE
        dtCriacao > '2021-05-08' AND
        dtCriacao < '2021-05-16'
    GROUP BY
        assunto
    ORDER BY
        total DESC
    LIMIT
        5
) b ON
    a.`assunto` = b.`assunto`
WHERE
    a.dtCriacao > '2021-05-08' AND
    a.dtCriacao < '2021-05-16'