[BNM] help with mysql query

[Ali]

sorry just seen this thread.

Looking at the group by suggestions the column names look wrong maybe try:

GROUP BY ContactLog.Contact, ORDER BY ContactLog.Date DESC

this would seem to be the most efficient way to see the last date a log was 
made for each contact.

hth

ali


----- Original Message ----- 
From: "Stuart Dunkeld" <stuartd at gmail.com>
To: "Brighton New Media" <bnmlist at brightonnewmedia.org>
Sent: Thursday, April 16, 2009 3:49 PM
Subject: Re: [BNM] help with mysql query


Does your version of mysql support subqueries (I think that's version
5)? if so, try adding this to the WHERE clause:

ContactLog.Date = (select max(ContactLog.Date) from ContactLog
innerContactLog where innerContactLog.Contact = contact.Id)

--stuart

On Thu, Apr 16, 2009 at 3:14 PM, Andrew Holway <andrew at moonet.co.uk> wrote:
> No, not quite.
>
> The GROUP BY Contact, ORDER BY Date DESC is not having any effect of
> the ContactLog record that it picks out.
>
> ContactLog.Note,
> date_format(ContactLog.Date,'%W %e %M %Y %T')
>
> Is there any way to persuade it to pick the record with the latest date?
>
> Ta
>
> Andrew
>
>
>
> On Thu, Apr 16, 2009 at 2:04 PM, Leo Brown <lists at acumendevelopment.net> 
> wrote:
>> Agreed- GROUP BY Contact, ORDER BY Date DESC
>>
>> -----Original Message-----
>> From: bnmlist-bounces at brightonnewmedia.org
>> [mailto:bnmlist-bounces at brightonnewmedia.org] On Behalf Of James Page
>> Sent: 16 April 2009 13:51
>> To: Brighton New Media
>> Subject: Re: [BNM] help with mysql query
>>
>> GROUP BY contact
>> or what else you want group them by....
>
>>
>>
>>
>> 2009/4/16 Andrew Holway <andrew at moonet.co.uk>
>>
>>> Nay,
>>>
>>> That just returns one row. (LIMIT 1 me thinks) I want to return one
>>> row(one with the latest date) from the ContactLog for each contact.
>>>
>>> ta
>>>
>>> Andy
>>>
>>> On Thu, Apr 16, 2009 at 1:32 PM, Leo Brown <lists at acumendevelopment.net>
>>> wrote:
>>> > Andy
>>> >
>>> > Just add this to the bottom:
>>> >
>>> > ORDER BY ContactLog.Date DESC
>>> > LIMIT 1
>>> >
>>> > Cheers
>>> > Leo
>>> >
>>> > -----Original Message-----
>>> > From: bnmlist-bounces at brightonnewmedia.org
>>> > [mailto:bnmlist-bounces at brightonnewmedia.org] On Behalf Of Andrew 
>>> > Holway
>>> > Sent: 16 April 2009 13:29
>>> > To: Brighton New Media
>>> > Subject: [BNM] help with mysql query
>>> >
>>> > I have the mysql query below that is working quite nice apart from it
>>> > pulling all the entries in ContactLog.Date. Is there anyway of making
>>> > it just pull up the last one (by date)? Trying to find all the
>>> > customers in our database that we havent spoken to for a long time.
>>> >
>>> > Thanks,
>>> >
>>> > Andy
>>> >
>>> >
>>> >
>>> > SELECT
>>> > Contact.ID,
>>> > Company.Company,
>>> > Contact.Title,
>>> > Contact.Initial,
>>> > Contact.FirstName,
>>> > Contact.Prefix,
>>> > Contact.SurName,
>>> >
>>> > ContactLog.Note,
>>> > -> date_format(ContactLog.Date,'%W %e %M %Y %T')
>>> >
>>> > FROM
>>> > Contact,Company,Status,Country,ContactLog
>>> > WHERE
>>> > Contact.CompanyID=Company.ID
>>> > AND
>>> > Contact.Country=Country.ID
>>> > AND
>>> > Contact.ID=ContactLog.Contact
>>> > AND
>>> > Status.ID='2'
>>> > AND
>>> > Country.ID='5'
>>> >
>>> > --
>>> >
>>> Moonet Ltd, registered in England and Wales No: 06330013 || VAT No: 921
>> 6412
>>> > 49
>>> > 1a Orleston road, London, N7 8LH || http://www.moonet.co.uk
>>> > Registered Office: Ross House, The Square, Stow on the Wold,
>>> > Cheltenham, Glos, GL54 1AF
>>> > --
>>> >
>>> > BNM Subscribe/Unsubscribe:
>>> > http://www.brightonnewmedia.org/options/bnmlist
>>> >
>>> > BNM powered by Wessex Networks:
>>> > http://www.wessexnetworks.com
>>> >
>>> > --
>>> >
>>> > BNM Subscribe/Unsubscribe:
>>> > http://www.brightonnewmedia.org/options/bnmlist
>>> >
>>> > BNM powered by Wessex Networks:
>>> > http://www.wessexnetworks.com
>>> >
>>>
>>>
>>>
>>> --
>>>
>>> Moonet Ltd, registered in England and Wales No: 06330013 || VAT No: 921
>> 6412 49
>>> 1a Orleston road, London, N7 8LH || http://www.moonet.co.uk
>>> Registered Office: Ross House, The Square, Stow on the Wold,
>>> Cheltenham, Glos, GL54 1AF
>>> --
>>>
>>> BNM Subscribe/Unsubscribe:
>>> http://www.brightonnewmedia.org/options/bnmlist
>>>
>>> BNM powered by Wessex Networks:
>>> http://www.wessexnetworks.com
>>>
>> --
>>
>> BNM Subscribe/Unsubscribe:
>> http://www.brightonnewmedia.org/options/bnmlist
>>
>> BNM powered by Wessex Networks:
>> http://www.wessexnetworks.com
>>
>> --
>>
>> BNM Subscribe/Unsubscribe:
>> http://www.brightonnewmedia.org/options/bnmlist
>>
>> BNM powered by Wessex Networks:
>> http://www.wessexnetworks.com
>>
>
>
>
> --
> Moonet Ltd, registered in England and Wales No: 06330013 || VAT No: 921 
> 6412 49
> 1a Orleston road, London, N7 8LH || http://www.moonet.co.uk
> Registered Office: Ross House, The Square, Stow on the Wold,
> Cheltenham, Glos, GL54 1AF
> --
>
> BNM Subscribe/Unsubscribe:
> http://www.brightonnewmedia.org/options/bnmlist
>
> BNM powered by Wessex Networks:
> http://www.wessexnetworks.com
>
-- 

BNM Subscribe/Unsubscribe:
http://www.brightonnewmedia.org/options/bnmlist

BNM powered by Wessex Networks:
http://www.wessexnetworks.com