List:General Discussion« Previous MessageNext Message »
From:Gavin Towey Date:September 16 2009 11:33pm
Subject:RE: Datediff function
View as plain text  
Hi John,

You can't use aggregate function in the WHERE clause, because they aren't evaluated until
after the WHERE clause is applied.

Wouldn't it be much easier to simply keep a last_tweet_date field updated somewhere then
simply do
SELECT USER_NAME FROM USERS WHERE last_tweet_date < NOW()-INTERVAL 7 DAY; ?

Regards,
Gavin Towey

-----Original Message-----
From: John Meyer [mailto:johnmeyer@stripped]
Sent: Wednesday, September 16, 2009 12:52 PM
To: mysql@stripped
Subject: Datediff function

I'm trying to pull up a list of users who haven't tweeted in 7 or more
days, and I'm trying to use this statement:
SELECT USER_NAME, MAX(TWEET_CREATEDAT) FROM USERS NATURAL JOIN TWEETS
WHERE DATEDIFF(NOW(),MAX(TWEET_CREATEDAT)) > 7 GROUP BY USERS.USER_ID

But it says "invalid group function".  How should I reword this query?

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1


The information contained in this transmission may contain privileged and confidential
information. It is intended only for the use of the person(s) named above. If you are not
the intended recipient, you are hereby notified that any review, dissemination,
distribution or duplication of this communication is strictly prohibited. If you are not
the intended recipient, please contact the sender by reply email and destroy all copies
of the original message.
Thread
Datediff functionJohn Meyer16 Sep
  • RE: Datediff functionGavin Towey17 Sep
    • Re: Datediff functionJohn Meyer17 Sep
      • RE: Datediff functionGavin Towey17 Sep
    • RE: Datediff functionJerry Schwartz17 Sep
  • Re: Datediff functionShawn Green17 Sep