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 ?
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
)
Try this
SELECT *
FROM Table
WHERE Conversion_Rate != 0
ORDER BY ABS(DATEDIFF(future_dates, NOW()))
LIMIT 10
or
SELECT *
FROM table
WHERE Conversion_Rate != 0
ORDER BY ABS(now() - future_dates) DESC
limit 10
Future_date should be '13/08/2015'. This record should be display today. I think you may be want this query.
select * from events where CURRENT_DATE - INTERVAL 5 DAY <=future_dates and Conversion_Rate != 0
select *
from events
where Conversion_Rate != '0'
order by ABS(DATEDIFF(CURDATE(),future_dates )) asc