How do I select the last dte in MySql WHERE NOT EXIST

Question

I am testing in a PHP web application if a user has logged in, in the last three months. If the user has not logged in, I send a reminder email to the user. I have a code that does work, except for the fact that the code selects the first date in the database (MySql), and not the last one.

Here is some sample data: All login dates for test user:

SQL select the first date instead of the last date:

I expected to see the date 2020-06-21

This is my last code attempt:

SELECT Client, IP, Date
FROM iplog ip1
WHERE NOT EXISTS (SELECT 1 FROM iplog ip2 
              WHERE ip1.Client = ip2.Client AND
                    ip2.Date >= DATE_SUB(NOW(), INTERVAL 3 MONTH)ORDER BY Date DESC)
                    GROUP BY Client
                    ORDER BY Date DESC
                    

Answer 1

Isn't it simpler done with aggregation?

select client, max(date) last_login_date
from iplog 
group by client
having max(date) <= now() - interval 3 month

This brings the clients that did not login during the last 3 months, along with their last login date.

Note that this query can not identify clients that never logged in (because they do not appear in the log table). In normal situations, you would have a separate table that stores the clients, and then:

select c.*
from clients c
where not exists (
    select 1 from iplog l where l.client = c.client and l.date > now() - interval 3 month
)