PHP How To Return Latest Entries Per Set Of Users?

Question

I am trying to return back the latest message based on timestamp from a group of Senders.

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!

liamgriffiths · Answer 1 · 2011-09-08T22:02:38.843

0

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

edited Sep 08 '11 at 22:02

answered Sep 08 '11 at 21:48

liamgriffiths

348
2
8

I always treated that usage of `GROUP BY` as unreliable, is I couldn't find any documentation for it's use (using fields that are NOT listed in the `GROUP BY` clause, and NOT wrapped in an aggregate function). I figured it was a MySQL quirk that couldn't be counted on (most other RDBMS's would reject this statement, I believe). – Doug Kress Sep 08 '11 at 21:54
@liamgriffiths This is getting there! It now returns each Sender's message but not Joe's latest. It is returning Joe's earliest. I tried ASC and that just changed the order in which the entire return was sorted. – Newphper Sep 08 '11 at 23:03

score 0 · Answer 2 · edited Jan 16 '12 at 12:31

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

score 0 · Answer 3 · edited May 23 '17 at 10:24

0

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.

edited May 23 '17 at 10:24

Community

1
1

answered Jan 16 '12 at 13:12

newtover

31,286
11
84
89

PHP How To Return Latest Entries Per Set Of Users?

3 Answers3