> 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);
Here I am getting the errors: mysql> SELECT @latest:= MAX(dateCreated); ERROR 1054: Unknown column 'dateCreated' in 'field list' mysql> SELECT @latest:= MAX(user.dateCreated); ERROR 1109: Unknown table 'user' in field list This works: SELECT @latest:= MAX(dateCreated) FROM user; > > SELECT * FROM user > WHERE validated LIKE 'N' > AND dateCreated <= DATE_SUB(@latest, INTERVAL 10 DAY); Sorry for my newness here, but what is this technique called? You are creating a variable called @latest. I'd like to look this up in the doc. > > 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? > That's absolutely right. I'm already using nntp to keep the clock in synch, but as that relies on several outside factors (nntp server, 'net connection) I was trying to cleverly come up with something more reliable. We get about 150 validated users / day, so I figured that latest registered user would be a reliable place to count backwards from, until signups drop off, and even then this algorithm would err on the side of NOT selecting. You saw the flaw in my logic, though. I'll have to come up with something better. Thanks again for the help and I welcome any other suggestions. Jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
