MySQL Group By single result order by time

Question

I am trying to fetch users from messages table which has send message to me but need only one result per sender which is max of creation column which is time of message creation

SELECT messages.from, account_images.profile, bio.user_full_name
FROM messages
INNER JOIN account_images ON account_images.uuid=messages.from
INNER JOIN bio ON bio.uuid=messages.from
WHERE messages.to='me'
GROUP BY messages.from
ORDER BY messages.creation DESC

A user whose message is created recently must be on top but it not coming on top using this code. I referred php mysql Group By to get latest record, not first record but not getting anything Any help?

How are your tables structured? – Leonardo Jan 23 '14 at 18:44 — Leonardo, Jan 23 '14 at 18:44
Another victim of MySQL's evil `GROUP BY` behavior. – D'Arcy Rittich Jan 23 '14 at 18:46 — D'Arcy Rittich, Jan 23 '14 at 18:46
Table Structure: `id || from || to || message || creation` – user3222855 Jan 23 '14 at 18:52 — user3222855, Jan 23 '14 at 18:52

score 0 · Answer 1 · answered Jan 23 '14 at 20:02

0

I found this mysql doc very helpful in cases like this: http://dev.mysql.com/doc/refman/5.0/en/example-maximum-row.html

SELECT * 
FROM messages m
LEFT JOIN messages m2 ON m.creation > m2.creation
WHERE m2.creation IS NULL
AND m1.to='me'

answered Jan 23 '14 at 20:02

Digital Chris

6,177
1
20
29

score 0 · Accepted Answer · answered Jan 23 '14 at 20:14

You could have written the current query as...

SELECT DISTINCT messages.from, account_images.profile, bio.user_full_name
FROM messages
INNER JOIN account_images ON account_images.uuid=messages.from
INNER JOIN bio ON bio.uuid=messages.from
WHERE messages.to='me'

Which would have been marginally more efficient - but as you will have noticed with your current query, it doesn't get sorted by the most recent message.

This is sorted as you request:

SELECT messages.from, account_images.profile, bio.user_full_name
FROM messages
INNER JOIN account_images ON account_images.uuid=messages.from
INNER JOIN bio ON bio.uuid=messages.from
WHERE messages.to='me'
GROUP BY messages.from
ORDER BY MAX(messages.creation) DESC;

It is possible to get the result you desire using DISTINCT instead of aggregation, but it's neither efficient nor elegant.

MySQL Group By single result order by time

2 Answers2