How to get the previous 4 records in MySQL based on where the condition is

Question

I have data in mysql table, I want to get the latest records from this condition. For example: I want to pass month = '11' and year = '2017' and select month_id 11, 10, 9 and 8.

I added the following data here: http://sqlfiddle.com/#!9/eb01c5/2

Thank.

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

SQL can be written something like this:

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 easier if the year + month was saved as a composite value - for example. the first month, but the same logic applies to searching for "n records before."

Answer 3

select * from mt_month where month_id<=11 order by month_id desc limit 4

Answer 4

Since you know the month from which you want to receive data, why not use it select * from mt_month where start_date BETWEEN '2017-08-01' AND '2017-11-01'to return your data? Rather, thanks to pass month_id, you can pass the start date and end date for your request

Answer 5

You can do something like this example:

select * from mt_month
 where year=2017 AND month=11
 order By year Desc limit 4;