PHP How To Return Latest Entries Per Set Of Users?

I am trying to return back the latest message based on timestamp from a group of Senders. 开发者_如何学Python

The basic table and qry looks like this

SELECT senderName, messages
FROM MessageTable
WHERE receiverID = 1

(output)
Joe "Hey"
Bob "Yo"
Jim "Blah"
Joe "What's up"

I want to get the latest message from EACH sender. I thought to use ORDER BY and LIMIT but it just returns 1 message from the entire qry. Which is not the result I’m looking for.

SELECT senderName, messages
FROM MessageTable
WHERE receiverID = 1
ORDER BY sentDate ASC LIMIT 1

(output)
Bob "Yo"

I would like to have this kind of output, where only Joe's latest message is returned along with the other Sender's latest message.

Bob "Yo"
Jim "Blah"
Joe "What's up"

I've looked at another question on stackoverflow, but it didn't make sense to me. https://stackoverflow.com/questions/384142/how-to-get-latest-record-for-each-day-when-there-are-multiple-entries-per-day

Thank you!

GROUP BY is your friend http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html

You could do some thing like this:

SELECT senderName, messages
FROM MessageTable
GROUP BY senderName
ORDER BY sentDate DESC

Ok, after some searching and trial and error, this query returned what i was looking for:

SELECT a.senderName, a.messages, a.sentDate
FROM MessageTable AS a,
(
    SELECT senderName, MAX(sentDate) as max_date
    FROM MessageTable
    WHERE ReceiverID = 1
    GROUP BY senderName
) AS b
WHERE a.senderName = b.senderName
AND a.sentDate = b.max_date;

All the credit goes to this site: http://forums.devarticles.com/general-sql-development-47/select-max-datetime-problem-10210.html

The type of the queries is called "Ranking queries" and they do usually require some level of competence. Here is a similar question: Retrieving the last record in each group

It is better if you would operate with numeric ids instead of strings to make the query efficient enough.