SQL Select last 6 month from a table that saves year and month in columns

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

Contents hide

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Following approach should work:

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

Method 2

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;

This might not run but something like this should do the trick for february.

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

Easiest way is to use a regular date format that sql understands from the beginning.

Method 3

The below query returns the last 6 months’ values based on the current date.
If I understand correctly you are trying something like:

SELECT table_id, some_id, some_other_id,mt_year,mt_month
FROM (
SELECT table_id, some_id, some_other_id, 
  mt_year , 
  mt_month ,
  @order_rank := IF(@current_month = mt_month,
  @order_rank + 1, 1) AS order_rank,
  @current_month := mt_month 
FROM mytable
ORDER BY   mt_year ,mt_month , some_id DESC ) ranked_orders
WHERE order_rank <= 5
and
mt_month BETWEEN MONTH((CURRENT_DATE() - INTERVAL 6 MONTH)) AND MONTH(CURRENT_DATE());

Demo: https://www.db-fiddle.com/f/iRmvLZs9L8Tk1zJFgTMusX/2

Try on your data and let me know.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating