Search code examples
mysqlsqlcoalesce

MySQL - Using COALESCE with DATE_ADD and DATE_SUB to get next/previous record

I am trying to query MySQL to select the previous and next record. I need help in using COALESCE and DATE_ADD/DATE_SUB together.

SELECT * from `Historical` where `DeltaH` = 'ALTF' and `Date`= 
COALESCE(DATE_SUB('2019-01-21', INTERVAL 1 DAY),
DATE_SUB('2019-01-21',INTERVAL 2 DAY),
DATE_SUB('2019-01-21', INTERVAL 3 DAY));

I cannot use the primary key because rows in the table are/will be deleted. The date column also does not necessarily have fixed dates, what I want to find is the next earlier/later date.

SELECT * from `Historical` where `DeltaH` = 'ALTF' and `Date`=  
DATE_SUB('2019-01-21', INTERVAL 3 DAY);

The above query seems to work, however I need to query for INTERVAL 1 DAY, in case the date does not exist move to INTERVAL 2 DAY....

 select * from `Historical` where `DeltaH` = 'ALTF' and `Date`= 
 DATE_SUB('2019-01-21', INTERVAL COALESCE(1,2,3,4,5) DAY);

This one does not work either. I understand that the COALESCE() function returns the first non-null value, however I am not able to get it to work using the above query. I have confirmed that data exists for 2019-01-18 but is not being selected. Can you please advise?

I am OK with using an alternate solution.

Solution

  • You can use a subquery to find the most recent date in the table that is less than 2019-01-21 e.g.

    SELECT * 
FROM `Historical`
WHERE `DeltaH` = 'ALTF' AND `Date`= (SELECT MAX(`Date`)
                                     FROM `Historical`
                                     WHERE `DeltaH` = 'ALTF' AND `Date` < '2019-01-21')

    To find the closest date that is later, we just adapt the query slightly, using MIN and >:

    SELECT * 
FROM `Historical`
WHERE `DeltaH` = 'ALTF' AND `Date`= (SELECT MIN(`Date`)
                                     FROM `Historical`
                                     WHERE `DeltaH` = 'ALTF' AND `Date` > '2019-01-21')