jim wrote:
Hi,
The task is to find users whose accounts have not been validated for the last 10 days. This SELECT accomplishes that:
SELECT * FROM user WHERE validated LIKE 'N' AND dateCreated <= DATE_SUB(CURDATE(), INTERVAL 10 DAY)
But, using curdate() is dangerous. What if the system time is messed up? Instead, would be better to use MAX(dateCreated), but that does not work.
This does not work:
SELECT * FROM user WHERE validated LIKE 'N' AND dateCreated <= DATE_SUB(MAX(dateCreated), INTERVAL 10 DAY)
Error is 'Invalid use of group function " even if a GROUP BY is added.
Is there a way to do this, so that the SELECT can get only values for
users who have validated LIKE 'N' and their dateCreated is 10 days
before the latest dateCreated?
Thanks for your help.
Jim
Right. MAX() returns the largest value *from the selected rows*. The WHERE clause determines which rows to look at. You can't calculate the MAX based on those rows till after you've chosen them, so you can't choose them based on the MAX.
One solution would be to use a variable:
SELECT @latest:= MAX(dateCreated);
SELECT * FROM user WHERE validated LIKE 'N' AND dateCreated <= DATE_SUB(@latest, INTERVAL 10 DAY);
I wonder if this is really the best way, though. What happens if nobody validated today (it's a holiday, perhaps)? More to the point, is this query immune to system time errors? I think not. Isn't dateCreated based on system time? If your system time is unreliable, your dateCreated is also unreliable. This is a big problem. Suppose system time is accurate now, as we run the query, but was off some time yesterday. If even one row has a dateCreated more than 10 days from now, you'll get almost every row with the MAX(dateCreated) version. And the incorrect row(s) will continue to appear validated long after it(they) should. On the other hand, if system time was wrong by 10 days or more in the other direction yesterday, accounts validated during that period will show up as unvalidated in either query. I think you are better off fixing your system time than trying to work around it. Couldn't you use a timeserver to keep in sync?
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
