Rauno Rauno - 1 month ago 7
MySQL Question

SQL max date related issue

I'm having a bit of an issue with max(date) in SQL.
Basically the problem being that I have to check if latest date entered by id is more than 1 days old and then return that date.

id| user_id| send_date
8 | 90 | 2016-10-21 14:31:14
| 10 | 90 | 2016-10-25 09:56:28
| 11 | 18 | 2016-10-22 09:56:28
| 12 | 19 | 2016-10-21 09:56:28
| 13 | 19 | 2016-10-23 09:56:28
| 13 | 20 | 2016-10-25 09:56:28


This is part of a much longer SQL (just the part that I have a problem with):

SELECT max(h.send_date) as lastSent
FROM history h
WHERE (h.send_date < NOW() - INTERVAL 1 DAY);


Now what happens is that instead of selecting rows where latest entered date is older than 1 day, I get the latest one that is older than 1 day even if there's a newer entry in the table.

Does anyone have an idea how to change it so that SQL would only return the latest date when it's older that 24h and the newest (by user) in the table (in the example, it would have to return nothing because there's an entry less than 24h old)?

Edited the table example a bit. This is what I need to get as a result (user_ids 90 and 20 get's ignored because of 2016-10-25 09:56:28):

18 | 2016-10-22 09:56:28
19 | 2016-10-23 09:56:28

Answer

for aggregation function you should use having and not where

SELECT max(h.send_date) as lastSent 
FROM history h 
having  max(h.send_date ) < DATE_SUB(NOW() ,INTERVAL 1 DAY) ;