Ahh... sweet... makes sense... thanks! :-)
On 3/12/07, Brent Baisley <[EMAIL PROTECTED]> wrote:
If you are looking for the latest created_at date, then you want to be grabbing
the max value of that field.
SELECT DISTINCT from_user_id, max(created_at) FROM messages WHERE to_user_id
= 1 GROUP BY from_user_id;
In your original query I think you meant to select from, not to, since to will
be 1;
----- Original Message -----
From: "John Kopanas" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Sunday, March 11, 2007 12:59 PM
Subject: Re: Finding a Solution To A Distinct Problem of Mine
>I think I got it:
>
> SELECT * FROM (SELECT * FROM messages ORDER BY created_at DESC) as
> messages WHERE to_user_id = 1 GROUP BY from_user_id;
>
> Is this the best way about it... or are their better ways you suggest?
>
> On 3/11/07, John Kopanas <[EMAIL PROTECTED]> wrote:
>> I have the following table:
>>
>> messages:
>> if:
>> from_user_id:
>> to_user_id:
>> body:
>> created_at:
>> updated_at:
>>
>> I have to return all the rows that have a distinct from_user_id based
>> on a to_user_id. Not only that but I want to return the rows with the
>> newest created_at.
>>
>> I thought this would work:
>> SELECT DISTINCT to_user_id, created_at FROM messages WHERE to_user_id
>> = 1 ORDER BY created_at;
>>
>> But the problem is that I only get distincts when I only have
>> to_user_id in the SELECT clause. Any suggestions.
>>
>> I need to return everything on the latest row that has a distinct
>> from_user_id :-).
>>
>> --
>> John Kopanas
>> [EMAIL PROTECTED]
>>
>> http://www.kopanas.com
>> http://www.cusec.net
>> http://www.soen.info
>>
>
>
> --
> John Kopanas
> [EMAIL PROTECTED]
>
> http://www.kopanas.com
> http://www.cusec.net
> http://www.soen.info
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
--
John Kopanas
[EMAIL PROTECTED]
http://www.kopanas.com
http://www.cusec.net
http://www.soen.info
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
