如何从mysql中获取当前日期的最近日期值

Question

Events Table

This is my events table what i need is i need to get the nearest date values from current date.

Current Table

--------------- --------------------------------------
events          future_dates      Conversion_Rate
--------------- --------------------------------------
One 1            01-08-2015        1000
One 1            03-08-2015        1000
One 1            06-08-2015        1000
One 1            07-08-2015        1000
One 1            10-08-2015        0
Two 1            13-08-2015        0
Two 1            14-08-2015        0
Two 1            16-08-2015        0
------------------------------------------------------

My Expected Result

--------------- --------------------------------------
events          future_dates      Conversion_Rate
--------------- --------------------------------------
One 1            07-08-2015        1000
One 1            10-08-2015        0
Two 1            13-08-2015        0
Two 1            14-08-2015        0
Two 1            16-08-2015        0
------------------------------------------------------

What i have tried so far

select * from events where CURDATE() < Conversion_Rate

What i need is i need to get the nearest date from current date and where conversion rate != '0'.How can i get the expected result in mysql ?

Answer 1

If I understand correct you want to fetch a latest record before current date which has a non zero conversion rate along with all other records from current date and later.

If so, a UNION may help you as shown in the following example:

(
select * from table_name 
 where conversion_rate <> 0 
   and future_dates < current_date
 order by future_dates desc limit 1
) -- this fetches pre cur date recs
union
( -- this fetches cur and post cur date recs
select * from table_name 
 where future_dates >= current_date
)