John Meyer wrote:
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
But it says "invalid group function". How should I reword this query?
Have you tried this?
SELECT USER_NAME
, MAX(TWEET_CREATEDATE) as latest_tweet
FROM USERS
NATURAL JOIN TWEETS
GROUP BY USERS.USER_ID
HAVING DATEDIFF(NOW(),latest_tweet) > 7;
OR you could build a distinct list (temporary table) of all users who
*have* tweeted in the last 7 days and LEFT JOIN the USERS table to that
to figure out who isn't on the list.
By moving the evaluation to the HAVING clause (which is evaluated after
the GROUP BY) you get to filter on the results of the GROUPing
operations. The conditions of the WHERE clause are applied before any
GROUPing happens.
--
Shawn Green, MySQL Senior Support Engineer
Sun Microsystems, Inc.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
