Finding users who haven"t posted in a week

------=_NextPart_000_0104_01CA6046.C7A66AF0
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

I want to get a list of all users who haven't posted in a week. But when I
use the following function.



select user_id, max(tweet_createdat) from tweets where
datediff(now(),max(tweet_createdat)) > 7;





Is producing the error:



Invalid use of group function






------=_NextPart_000_0104_01CA6046.C7A66AF0--

Re: Finding users who haven"t posted in a week

the function max(), among others, makes no sense in the absence of a
GROUP BY clause.

try adding "GROUP BY user_id"

- michael dykman

On Sun, Nov 8, 2009 at 9:40 AM, John Meyer
wrote:
> I want to get a list of all users who haven't posted in a week. But when I
> use the following function.
>
>
>
> select user_id, max(tweet_createdat) from tweets where
> datediff(now(),max(tweet_createdat)) > 7;
>
>
>
>
>
> Is producing the error:
>
>
>
> Invalid use of group function
>
>
>
>
>
>



--
- michael dykman
- mdykman@gmail.com

"May you live every day of your life."
Jonathan Swift

Larry's First Law of Language Redesign: Everyone wants the colon.

--
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: Finding users who haven"t posted in a week

Thanks, morning coffee hasn't kicked in. This worked out well.

select user_id, max(tweet_createdat) from tweets group by user_id having
datediff(now(),max(tweet_createdat)) > 7;

I forgot when to use the where and when to use the having clause.

-----Original Message-----
From: Michael Dykman [mailto:mdykman@gmail.com]
Sent: Sunday, November 08, 2009 8:35 AM
To: John Meyer
Cc: mysql@lists.mysql.com
Subject: Re: Finding users who haven't posted in a week

the function max(), among others, makes no sense in the absence of a
GROUP BY clause.

try adding "GROUP BY user_id"

- michael dykman

On Sun, Nov 8, 2009 at 9:40 AM, John Meyer
wrote:
> I want to get a list of all users who haven't posted in a week. But when I
> use the following function.
>
>
>
> select user_id, max(tweet_createdat) from tweets where
> datediff(now(),max(tweet_createdat)) > 7;
>
>
>
>
>
> Is producing the error:
>
>
>
> Invalid use of group function
>
>
>
>
>
>



--
- michael dykman
- mdykman@gmail.com

"May you live every day of your life."
Jonathan Swift

Larry's First Law of Language Redesign: Everyone wants the colon.
No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 8.5.425 / Virus Database: 270.14.52/2484 - Release Date: 11/08/09
07:37:00


--
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: Finding users who haven"t posted in a week

Now I'm wondering if I can use this query in an update to set a variable in
a second table

Users
------
User_id VARCHAR(50)
.. . .
User_active BITINT(1)

I want to set user_active to 0 where the user_id is in the query below.

select user_id, max(tweet_createdat) from tweets group by user_id having
datediff(now(),max(tweet_createdat)) > 7;


-----Original Message-----
From: John Meyer [mailto:johnmeyer@pueblocomputing.com]
Sent: Sunday, November 08, 2009 9:45 AM
To: 'Michael Dykman'
Cc: mysql@lists.mysql.com
Subject: RE: Finding users who haven't posted in a week

Thanks, morning coffee hasn't kicked in. This worked out well.

select user_id, max(tweet_createdat) from tweets group by user_id having
datediff(now(),max(tweet_createdat)) > 7;

I forgot when to use the where and when to use the having clause.

-----Original Message-----
From: Michael Dykman [mailto:mdykman@gmail.com]
Sent: Sunday, November 08, 2009 8:35 AM
To: John Meyer
Cc: mysql@lists.mysql.com
Subject: Re: Finding users who haven't posted in a week

the function max(), among others, makes no sense in the absence of a
GROUP BY clause.

try adding "GROUP BY user_id"

- michael dykman

On Sun, Nov 8, 2009 at 9:40 AM, John Meyer
wrote:
> I want to get a list of all users who haven't posted in a week. But when I
> use the following function.
>
>
>
> select user_id, max(tweet_createdat) from tweets where
> datediff(now(),max(tweet_createdat)) > 7;
>
>
>
>
>
> Is producing the error:
>
>
>
> Invalid use of group function
>
>
>
>
>
>



--
- michael dykman
- mdykman@gmail.com

"May you live every day of your life."
Jonathan Swift

Larry's First Law of Language Redesign: Everyone wants the colon.
No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 8.5.425 / Virus Database: 270.14.52/2484 - Release Date: 11/08/09
07:37:00


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=john.l.meyer@gmail.com

No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 8.5.425 / Virus Database: 270.14.52/2484 - Release Date: 11/08/09
07:37:00


--
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: Finding users who haven"t posted in a week

At 12:13 PM 11/8/2009, John Meyer wrote:
>Now I'm wondering if I can use this query in an update to set a variable in
>a second table
>
>Users
>------
>User_id VARCHAR(50)
>. . .
>User_active BITINT(1)
>
>I want to set user_active to 0 where the user_id is in the query below.
>
>select user_id, max(tweet_createdat) from tweets group by user_id having
>datediff(now(),max(tweet_createdat)) > 7;


You can try something like this:

update table2 set Slackers='Y' where user_id in (select user_id from tweets
group by user_id having
datediff(now(),max(tweet_createdat)) > 7);

Mike



>-----Original Message-----
>From: John Meyer [mailto:johnmeyer@pueblocomputing.com]
>Sent: Sunday, November 08, 2009 9:45 AM
>To: 'Michael Dykman'
>Cc: mysql@lists.mysql.com
>Subject: RE: Finding users who haven't posted in a week
>
>Thanks, morning coffee hasn't kicked in. This worked out well.
>
>select user_id, max(tweet_createdat) from tweets group by user_id having
>datediff(now(),max(tweet_createdat)) > 7;
>
>I forgot when to use the where and when to use the having clause.
>
>-----Original Message-----
>From: Michael Dykman [mailto:mdykman@gmail.com]
>Sent: Sunday, November 08, 2009 8:35 AM
>To: John Meyer
>Cc: mysql@lists.mysql.com
>Subject: Re: Finding users who haven't posted in a week
>
>the function max(), among others, makes no sense in the absence of a
>GROUP BY clause.
>
>try adding "GROUP BY user_id"
>
> - michael dykman
>
>On Sun, Nov 8, 2009 at 9:40 AM, John Meyer
> wrote:
> > I want to get a list of all users who haven't posted in a week. But when I
> > use the following function.
> >
> >
> >
> > select user_id, max(tweet_createdat) from tweets where
> > datediff(now(),max(tweet_createdat)) > 7;
> >
> >
> >
> >
> >
> > Is producing the error:
> >
> >
> >
> > Invalid use of group function
> >
> >
> >
> >
> >
> >
>
>
>
>--
> - michael dykman
> - mdykman@gmail.com
>
>"May you live every day of your life."
> Jonathan Swift
>
>Larry's First Law of Language Redesign: Everyone wants the colon.
>No virus found in this incoming message.
>Checked by AVG - www.avg.com
>Version: 8.5.425 / Virus Database: 270.14.52/2484 - Release Date: 11/08/09
>07:37:00
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=john.l.meyer@gmail.com
>
>No virus found in this incoming message.
>Checked by AVG - www.avg.com
>Version: 8.5.425 / Virus Database: 270.14.52/2484 - Release Date: 11/08/09
>07:37:00
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=mos99@fastmail.fm


--
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: Finding users who haven"t posted in a week

--0016e64cc90edfbecb047801d82e
Content-Type: text/plain; charset=ISO-8859-1

make sure u have a "WHERE CLAUSE" for the update , otherwise, entire data
for that column will be seto "Y"

On Mon, Nov 9, 2009 at 9:10 PM, mos wrote:

> At 12:13 PM 11/8/2009, John Meyer wrote:
>
>> Now I'm wondering if I can use this query in an update to set a variable
>> in
>> a second table
>>
>> Users
>> ------
>> User_id VARCHAR(50)
>> . . .
>> User_active BITINT(1)
>>
>> I want to set user_active to 0 where the user_id is in the query below.
>>
>> select user_id, max(tweet_createdat) from tweets group by user_id having
>> datediff(now(),max(tweet_createdat)) > 7;
>>
>
>
> You can try something like this:
>
> update table2 set Slackers='Y' where user_id in (select user_id from tweets
> group by user_id having
> datediff(now(),max(tweet_createdat)) > 7);
>
> Mike
>
>
>
> -----Original Message-----
>> From: John Meyer [mailto:johnmeyer@pueblocomputing.com]
>> Sent: Sunday, November 08, 2009 9:45 AM
>> To: 'Michael Dykman'
>> Cc: mysql@lists.mysql.com
>> Subject: RE: Finding users who haven't posted in a week
>>
>> Thanks, morning coffee hasn't kicked in. This worked out well.
>>
>> select user_id, max(tweet_createdat) from tweets group by user_id having
>> datediff(now(),max(tweet_createdat)) > 7;
>>
>> I forgot when to use the where and when to use the having clause.
>>
>> -----Original Message-----
>> From: Michael Dykman [mailto:mdykman@gmail.com]
>> Sent: Sunday, November 08, 2009 8:35 AM
>> To: John Meyer
>> Cc: mysql@lists.mysql.com
>> Subject: Re: Finding users who haven't posted in a week
>>
>> the function max(), among others, makes no sense in the absence of a
>> GROUP BY clause.
>>
>> try adding "GROUP BY user_id"
>>
>> - michael dykman
>>
>> On Sun, Nov 8, 2009 at 9:40 AM, John Meyer
>> wrote:
>> > I want to get a list of all users who haven't posted in a week. But when
>> I
>> > use the following function.
>> >
>> >
>> >
>> > select user_id, max(tweet_createdat) from tweets where
>> > datediff(now(),max(tweet_createdat)) > 7;
>> >
>> >
>> >
>> >
>> >
>> > Is producing the error:
>> >
>> >
>> >
>> > Invalid use of group function
>> >
>> >
>> >
>> >
>> >
>> >
>>
>>
>>
>> --
>> - michael dykman
>> - mdykman@gmail.com
>>
>> "May you live every day of your life."
>> Jonathan Swift
>>
>> Larry's First Law of Language Redesign: Everyone wants the colon.
>> No virus found in this incoming message.
>> Checked by AVG - www.avg.com
>> Version: 8.5.425 / Virus Database: 270.14.52/2484 - Release Date: 11/08/09
>> 07:37:00
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:
>> http://lists.mysql.com/mysql?unsub=john.l.meyer@gmail.com
>>
>> No virus found in this incoming message.
>> Checked by AVG - www.avg.com
>> Version: 8.5.425 / Virus Database: 270.14.52/2484 - Release Date: 11/08/09
>> 07:37:00
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql?unsub=mos99@fastmail.fm
>>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=anandkl@gmail.com
>
>

--0016e64cc90edfbecb047801d82e--

Re: Finding users who haven"t posted in a week

Update tweets, t2
set t2.active=3D0
where tweets.user_id=3Dt2.user_id
and (rest of tweets where cond)

I'm on a mobile device, but that query should work, just do not leave
the tweets=3Dt2 cond out of the where.


On Monday, November 9, 2009, mos wrote:
> At 12:13 PM 11/8/2009, John Meyer wrote:
>
> Now I'm wondering if I can use this query in an update to set a variable =
in
> a second table
>
> Users
> ------
> User_id VARCHAR(50)
> . . .
> User_active BITINT(1)
>
> I want to set user_active to 0 where the user_id is in the query below.
>
> select user_id, max(tweet_createdat) from tweets group by user_id having
> datediff(now(),max(tweet_createdat)) > 7;
>
>
>
> You can try something like this:
>
> update table2 set Slackers=3D'Y' where user_id in (select user_id from tw=
eets group by user_id having
> datediff(now(),max(tweet_createdat)) > 7);
>
> Mike
>
>
>
>
> -----Original Message-----
> From: John Meyer [mailto:johnmeyer@pueblocomputing.com]
> Sent: Sunday, November 08, 2009 9:45 AM
> To: 'Michael Dykman'
> Cc: mysql@lists.mysql.com
> Subject: RE: Finding users who haven't posted in a week
>
> Thanks, morning coffee hasn't kicked in. =A0This worked out well.
>
> select user_id, max(tweet_createdat) from tweets group by user_id having
> datediff(now(),max(tweet_createdat)) > 7;
>
> I forgot when to use the where and when to use the having clause.
>
> -----Original Message-----
> From: Michael Dykman [mailto:mdykman@gmail.com]
> Sent: Sunday, November 08, 2009 8:35 AM
> To: John Meyer
> Cc: mysql@lists.mysql.com
> Subject: Re: Finding users who haven't posted in a week
>
> the function max(), among others, makes no sense in the absence of a
> GROUP BY clause.
>
> try adding "GROUP BY user_id"
>
> =A0- michael dykman
>
> On Sun, Nov 8, 2009 at 9:40 AM, John Meyer
> wrote:
>> I want to get a list of all users who haven't posted in a week. But when=
I
>> use the following function.
>>
>>
>>
>> select user_id, max(tweet_createdat) from tweets where
>> datediff(now(),max(tweet_createdat)) > 7;
>>
>>
>>
>>
>>
>> Is producing the error:
>>
>>
>>
>> Invalid use of group function
>>
>>
>>
>>
>>
>>
>
>
>
> --
> =A0- michael dykman
> =A0- mdykman@gmail.com
>
> "May you live every day of your life."
> =A0 =A0Jonathan Swift
>
> Larry's First Law of Language Redesign: Everyone wants the colon.
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 8.5.425 / Virus Database: 270.14.52/2484 - Release Date: 11/08/0=
9
> 07:37:00
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Djohn.l.meyer@=
gmail.com
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 8.5.425 / Virus Database: 270.14.52/2484 - Release Date: 11/08/0=
9
> 07:37:00
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dmos99@fastmai=
l.fm
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Djohnny@pixela=
ted.net
>
>

--=20
-----------------------------
Johnny Withers
601.209.4985
johnny@pixelated.net

--
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