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

43
February 11, 2019, at 07:20 AM

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.

Answer 1

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')
Answer 2

FWIW, I'd write this differently...

SELECT x.* 
  FROM Historical
  JOIN
     ( SELECT deltah
            , MAX(date) date
         FROM Historical
        WHERE date < '2019-01-21'
        GROUP 
           BY deltah
     ) y
    ON y.deltah = x.deltah
   AND y.date = x.date
 WHERE x.deltah = 'ALTF';
Answer 3

This seems like the simplest method:

select h.*
from historical h
where h.DeltaH = 'ALTF' and
      h2.Date < '2019-01-21'
order by h.Date DESC
limit 1

For best performance, you want an index on (DeltaH, Date).

If you want both the date before and after:

(select h.*
 from historical h
 where h.DeltaH = 'ALTF' and
       h2.Date < '2019-01-21'
 order by h.Date desc
 limit 1
) union all
(select h.*
 from historical h
 where h.DeltaH = 'ALTF' and
       h2.Date > '2019-01-21'
 order by h.Date asc
 limit 1
);

I'm not sure if one or both comparisons should be have =, so you can get results on that date.

READ ALSO
How to upgrade MySQL database schema in respective to sprint releases?

How to upgrade MySQL database schema in respective to sprint releases?

let say we have a Qt/qml based desktop application, this is using mysql as local db

38
Grant MySQL permissions on current database?

Grant MySQL permissions on current database?

I can grant permissions with a static database name in the GRANT statement

25
MySql Login error - Domain name added automatically with the user name

MySql Login error - Domain name added automatically with the user name

I am on Windows 10 desktop with a Cloudera VM running on itFrom inside the coudera vm Linux OS, I can login to MySQL with:

39