How to use a column's latest date in query?

Question

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.

Answer 1

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 !!!

Answer 2

It is not allowed to use an aggregate function that way, i.e. in the WHERE clause. One way to do it is to have the MAX() calculated in a subquery:

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

or with a derived table:

SELECT
    k.lastlogin,
    k.supemail,
    k.regionname
FROM
    smart_allusers_karen AS k
  CROSS JOIN
    (SELECT MAX(lastlogin) AS lastlogin FROM smart_allusers_karen) AS m
WHERE 
    k.lastlogin < m.lastlogin - INTERVAL 30 DAY 
  AND
    k.lastlogin > m.lastlogin - INTERVAL 60 DAY 
ORDER BY
    k.lastlogin ;