How to fetch previous 4 records in MySQL based on where condition

Question

I have data in mysql table, i want to fetch last records from given condition. example: i want to pass month = '11' and year = '2017' and fetch month_id's 11, 10, 9 and 8.

I have added sample data here: http://sqlfiddle.com/#!9/eb01c5/2

Thank you.

Answer 1

select * from `tablename` where year = 2017 AND month<=11 order by month desc  limit 4;

or

select * from `tablename` where year = 2017 AND month IN (11,10,9,8);

Answer 2

The SQL could be written something like so:

select *
from d
-- find all rows for this month or previous
where
   (year = 2017) and (month <= 11)  -- same month / earlier in same year
or (year < 2017)                    -- earlier year
-- for all found rows, order descending by year, then month
order by year desc, date desc
-- take the first 4 (descending by date)
limit 4

It would be simpler if the year+month were stored as a compound value - eg. the first of the month - but the same logic would apply to find the "n records before".