Mysql – How to use a column’s latest date in query

dateMySQL

SELECT
smart_allusers_karen.lastlogin,
smart_allusers_karen.supemail,
smart_allusers_karen.regionname
FROM
smart_allusers_karen
WHERE lastlogin < MAX(smart_allusers_karen.lastlogin) - INTERVAL 30 DAY AND
      lastlogin > MAX(smart_allusers_karen.lastlogin) - INTERVAL 60 DAY 
ORDER BY lastlogin

Getting error when trying to get the MAX date. Also not sure if this is the fastest way to do this.

Best Answer

Perhaps you can split the query up to catch the DateTimes in separate variables

SELECT lastlogin INTO @LastLogin FROM smart_allusers_karen ORDER BY lastlogin DESC LIMIT 1;
SET @ThirtyDaysAgo = @LastLogin - INTERVAL 30 DAY;
SET @SixtyDaysAgo  = @LastLogin - INTERVAL 60 DAY;
SELECT
    smart_allusers_karen.lastlogin,
    smart_allusers_karen.supemail,
    smart_allusers_karen.regionname
FROM
    smart_allusers_karen
WHERE lastlogin < @ThirtyDaysAgo  AND
      lastlogin > @SixtyDaysAgo 
ORDER BY lastlogin;

Please make sure lastlogin is indexed. If it is not, run this first:

ALTER TABLE smart_allusers_karen ADD INDEX (lastlogin);

Give it a Try !!!

Related Question