From: Peter Brawley
Date: April 18 2008 3:07pm
Subject: Re: A SQL Query Question
List-Archive: http://lists.mysql.com/mysql/212381
Message-Id: <4808B92C.5070008@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

>userId long
>picture MeduimBlob
>datePosted DateTime
>A userId can have many pictures posted. I want to write a 
>query that returns a distinct userId along with the most 
>recent picture posted. Can someone suggest an elegant and 
>fast query to accomplish this?

Latest pic for user N:

SELECT userID,MAX(dateposted)
FROM tbl
WHERE userID=N;

Latest pics per user:

SELECT t1.userID,t1.dateposted
FROM tbl t1
LEFT JOIN tbl t2 ON t1.userID=t2.userID AND t1.dateposted<t2.dateposted
WHERE t2.userID IS NULL;

PB