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=gcdmg-mysql-2@m.gmane.org

RE: Datediff function

Hi John,

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

Wouldn't it be much easier to simply keep a last_tweet_date field updated s=
omewhere 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@pueblocomputing.com]
Sent: Wednesday, September 16, 2009 12:52 PM
To: mysql@lists.mysql.com
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=3Dgtowey@ffn.com


The information contained in this transmission may contain privileged and c=
onfidential information. It is intended only for the use of the person(s) n=
amed above. If you are not the intended recipient, you are hereby notified =
that any review, dissemination, distribution or duplication of this communi=
cation is strictly prohibited. If you are not the intended recipient, pleas=
e contact the sender by reply email and destroy all copies of the original =
message.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Datediff function

Gavin Towey wrote:
> 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
>


I don't know if that would be so simple. I'd have to run programming
logic when I fetch the information off the twitter server. I just hoped
that there was a way to do it through SQL.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Datediff function

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=gcdmg-mysql-2@m.gmane.org

RE: Datediff function

>-----Original Message-----
>From: Gavin Towey [mailto:gtowey@ffn.com]
>Sent: Wednesday, September 16, 2009 7:34 PM
>To: John Meyer; mysql@lists.mysql.com
>Subject: RE: Datediff function
>
>Hi John,
>
>You can't use aggregate function in the WHERE clause, because they aren't
>evaluated until after the WHERE clause is applied.
>
[JS] You can use the HAVING clause, although it is probably inefficient.

Regards,

Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341

www.the-infoshop.com




>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@pueblocomputing.com]
>Sent: Wednesday, September 16, 2009 12:52 PM
>To: mysql@lists.mysql.com
>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=gtowey@ffn.com
>
>
>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.
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=jschwartz@the-
>infoshop.com





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Datediff function

Hi John,

If judicious transformation of data makes it easier to do the queries you w=
ant, then you should consider it. ETL isn't a common acronym in the databa=
se world just because we like three letters =3D) Though it depends on how =
often you're doing this, if it's one-off then it's probably not worth it, t=
hough I was making the assumption you're probably going to be using that qu=
ery frequently.

Regards,
Gavin Towey

-----Original Message-----
From: John Meyer [mailto:johnmeyer@pueblocomputing.com]
Sent: Wednesday, September 16, 2009 4:51 PM
To: Gavin Towey
Cc: mysql@lists.mysql.com
Subject: Re: Datediff function

Gavin Towey wrote:
> 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
>


I don't know if that would be so simple. I'd have to run programming
logic when I fetch the information off the twitter server. I just hoped
that there was a way to do it through SQL.

The information contained in this transmission may contain privileged and c=
onfidential information. It is intended only for the use of the person(s) n=
amed above. If you are not the intended recipient, you are hereby notified =
that any review, dissemination, distribution or duplication of this communi=
cation is strictly prohibited. If you are not the intended recipient, pleas=
e contact the sender by reply email and destroy all copies of the original =
message.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg