CODEWITHSUNDEEP
mysqlsql
albin
albin

Reputation: 53

MySQL Group by and Order

I'm trying to group messages like conversation and to display only the latest message from a conversation, therefore I need the one with the highest number in the column time.

This is my query:

SELECT * 
FROM console_msgs 
WHERE `to` = $user[id] 
GROUP BY `from` 
ORDER BY time DESC

Thanks for any help!

Upvotes: 1

Views: 54

Answers (3)

Rodney Adams
Rodney Adams

Reputation: 291

I don't quite understand the result set you need returned but I think you need to use the LIMIT keyword to only return the top result. Give this a try:

SELECT from, MAX(time) 
FROM console_msgs 
WHERE `to` = $user[id] 
GROUP BY `from` 
ORDER BY time DESC
LIMIT 1

Upvotes: 0

Milo LaMar
Milo LaMar

Reputation: 2256

You won't want to use GROUP BY to achieve what I think you're asking. You just want multiple order bys

SELECT * 
FROM console_msgs 
WHERE `to` = $user[id] 
ORDER BY `from`, time DESC

Upvotes: 0

eggyal
eggyal

Reputation: 125855

You want the groupwise maximum:

SELECT console_msgs.*
FROM   console_msgs NATURAL JOIN (
         SELECT   MAX(time) AS time
         FROM     console_msgs
         WHERE    `to` = $user[id]
         GROUP BY `from`
       ) t
WHERE  `to` = $user[id]

Upvotes: 1

Related Questions