I want to fetch data of last two months of each category from table.
Table looks like:
Id Year month category value
Expected output:
I tried using:
SELECT a.year,a.month,a.value, a.category
FROM test_data AS a
WHERE
(
SELECT COUNT(*)
FROM test_data AS b
WHERE b.category = a.category AND (b.year >= a.year AND b.month >= a.month)) <= 2
ORDER BY a.year DESC, a.month DESC
But it is giving extra record of TEST1 category. I guess because it is not working as expected for year condition. Please provide solution for this
Your first effort should go into fixing your data model and using a date
-like datatype to store the date information, rather than spreading it over different columns. This should be as simple as adding a new column to the table and updating it from the existing columns:
alter table test_data add mydate date;
update test_data set mydate = concat(year, '-', month, '-01');
That said, to make your current query work you need to fine-tune the conditions on the dates as follows. One option uses arithmetics:
SELECT a.year, a.month, a.value, a.category
FROM test_data AS a
WHERE (
SELECT COUNT(*)
FROM test_data AS b
WHERE
b.category = a.category
AND 100 * b.year + b.month >= 100 * a.year + a.month
) <= 2
ORDER BY a.year DESC, a.month DESC
Alternatively, if you are running MySQL 8.0, you can use row_number()
:
SELECT a.year, a.month, a.value, a.category
FROM (
SELECT
a.*,
ROW_NUMBER() OVER(PARTITION BY a.category ORDER BY a.year DESC, a.month DESC)
FROM test_data AS a
) a
WHERE rn <= 2