I have a table that saves year and month in columns and I need to get the top 5 grouping by an ID in the last 6 month.

I can easily get the information for a particular month an year but how can I get the same ranking of the top 5 for the last 6 month?

CREATE TABLE mytable (
table_id INT,  
some_id INT, 
some_other_id INT, 
mt_month INT, 
mt_year INT);

INSERT INTO mytable VALUES
("101","17","370","12","2021"),
("102","17","370","1","2021"),
("104","14","371","2","2021"),
("105","14","371","11","2021"),
("107","14","371","12","2021"),
("108","12","372","2","2021"),
("109","12","372","3","2021"),
("111","13","372","12","2021"),
("113","18","373","10","2021"),
("115","18","373","1","2021"),
("117","18","373","4","2021"),
("119","11","373","5","2021"),
("120","11","373","5","2021"),
("121","12","373","6","2021"),
("122","13","373","6","2021"),
("123","13","373","6","2021"),
("124","12","373","7","2021"),
("125","11","373","7","2021"),
("126","11","373","7","2021"),
("127","12","373","8","2021"),
("128","11","373","8","2021"),
("129","11","373","8","2021"),
("130","13","373","9","2021"),
("131","11","373","10","2021"),
("132","11","373","11","2021"),
("133","11","373","12","2021");

select some_id, min(mt_month), min(mt_year), count(table_id) as number from mytable group by some_id order by number DESC LIMIT 5;

https://www.db-fiddle.com/f/iRmvLZs9L8Tk1zJFgTMusX/0

CodePudding user response：

Following approach should work:

1. Filter the data for the last 6 months in the subquery (can be done by concatenating year and month and applying the relevant filter)
2. Use your logic of grouping in the outer query.

CodePudding user response：

This is what I came up with quickly. Made a variable to hold the wanted info and applied your same logic. Just specify time manually.

WITH a AS (
SELECT * FROM mytable
WHERE mt_year = 2021 and
mt_month BETWEEN 7 and 12 
)
select some_id, min(mt_month), min(mt_year), count(table_id) as number from a 
group by some_id
order by number DESC LIMIT 5;