1
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.

1
  • Just to let others know its use case - I have static reports that I combine with all kinds of employee data plus whatever. These reports come from like 10 different internal websites and are usually dumped monthly. So my options where to have a table that they manually enter their date range, use Now() which only makes their reports the day they run them or this. All of the sites are high traffic so anytime a report is ran it should have a lastlogin from that day.
    – LOSTinDB
    Commented Feb 6, 2014 at 18:24

2 Answers 2

2

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

1
  • OK I think I am getting slightly better with my mysql. I had the exact same thing original except I had SELECT lastlogin AS where you have INTO @LastLogin - which I am guess creates a variable to be used later? Either way I had lastlogin already indexed and it went through 200K rows in less than a half second.
    – LOSTinDB
    Commented Feb 6, 2014 at 18:18
1

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 ;
5
  • This works too but slower than Rolando's query.
    – LOSTinDB
    Commented Feb 6, 2014 at 18:19
  • I would expect efficiency to be almost identical between the 2nd query and Rolando's. Commented Feb 6, 2014 at 18:26
  • Maybe my configuration but about 5% slower - still very fast. Really wish mysql had a way to handle this without creating a helper view.
    – LOSTinDB
    Commented Feb 6, 2014 at 18:45
  • @LOSTinDB when you're optimizing and benchmarking queries, you want to use SELECT SQL_NO_CACHE ... which prevents the server from fooling you with a query running faster the 2nd time you run it. If the underlying tables don't change between runs, the server may server up cached results, skewing your timing measurements. Commented Feb 6, 2014 at 23:51
  • @Michael-sqlbot - I ran his second....
    – LOSTinDB
    Commented Feb 7, 2014 at 2:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.