Re: SELECT ... GROUP BY

[Michael Stassen]

Three solutions to this problem, including the one here, are discussed 
in the manual:

<http://www.mysql.com/doc/en/example-Maximum-column-group-row.html>

Michael

Darran Kartaschew wrote:

Excellent, that worked...

Yours Sincerely

Darran 
-----Original Message-----
From: Daniel Kasak [mailto:[EMAIL PROTECTED]
Sent: Monday, 8 March 2004 14:03
To: Darran Kartaschew; [EMAIL PROTECTED]
Subject: Re: SELECT ... GROUP BY
 
You need to do this in 2 steps.

Step 1:

select user_id, max(last_updated) as max_last_updated from posts group by user_id

You probably need to do this into a temporary table or something. Check the docs for more details, but you can do something like:

create temporary table tmp_latest_post_by_user ( select ........... ) to create temporary tables ( fill in the ......... )

Step 2:

select * from posts inner join tmp_latest_post_by_user
    on posts.user_id=tmp_latest_post_by_user
    and posts.last_updated=tmp_latest_post_by_user.max_last_updated
In this step you join the main table with the temporary table of latest posts by user, getting only the rows that match from both tables ( inner join ).

Dan




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]