Group By/Threaded Messaging MySQL Prob

Question

I'm building a private messaging system for a dating site..having troubles with a group by query. Here is the structure of the table:

`id` bigint (20) NOT NULL AUTO_INCREMENT , 
`fromme` integer (11) NOT NULL, 
`tome` integer (11) NOT NULL, 
`subject` varchar (255) NOT NULL, 
`message` longtext NOT NULL, 
`mydate` datetime NOT NULL, 
`thread` varchar (255) NOT NULL, 
`receipt` varchar (50) NOT NULL, 
`INDELETE` varchar (5), 
`SENTDELETE` varchar (5),
PRIMARY KEY (`id`)

When a user sends a new message to another user, it generates a random string to keep track of the thread. As they reply, it carries the thread string (similar to facebook). When a user logs in, they can see all the messages in their inbox, and based on whether it is a new message, it changes the bg color of the row. All of this is working fine, except the receipt status of a a message that has been threaded back and forth. This is the query:

select messages.id, messages.fromme, messages.subject, messages.message, messages.receipt, messages.mydate, messages.thread, users.firstname, users.lastname, users.image1
from messages, users
where messages.tome = '40' and messages.INDELETE !='y' and messages.fromme = users.id
GROUP BY messages.thread
ORDER BY messages.mydate desc

It returns it properly, but the group by function is returning the FIRST message of the thread..I need the LATEST one in order to make it work properly. Anyone know how to accomplish this?

Answer 1

To get the benefit of a GROUP BY clause you need an aggregate function (MAX, SUM, COUNT, etc). You probably want to use this subquery to work out the most recent (highest ID) message in each thread:

select MAX(messages.id) id, messages.thread 
FROM messages
GROUP BY messages.thread

Putting it all together you might use

SELECT messages.id, 
       messages.fromme, 
       messages.subject, 
       messages.message, 
       messages.receipt, 
       messages.mydate, 
       messages.thread, 
       users.firstname, users.lastname, users.image1
  FROM messages, 
       users,
       (SELECT MAX(messages.id) id, messages.thread thread 
          FROM messages
        GROUP BY messages.thread) latest,
 WHERE messages.tome = '40' 
   AND messages.INDELETE !='y' 
   AND messages.fromme = users.id
   AND messages.id=latest.id
ORDER BY messages.mydate desc

Answer 2

The answer @Ollie Jones is fine (except for outdated SQL-89 comma-style join syntax), but for what it's worth here's an alternative:

SELECT m1.*, u.*
FROM messages m1
LEFT OUTER JOIN messages m2
  ON (m1.thread=m2.thread AND m1.mydate < m2.mydate)
JOIN users u ON (m1.fromme = u.id)
WHERE m2.thread IS NULL;

No GROUP BY is needed. This matches each row m1 to a hypothetical row m2 that has the same thread and a later date. When there's no row m2 that matches, then m1 must be the latest message in the thread.

This solution is good for MySQL because MySQL usually creates a temporary table as it sorts out GROUP BY queries. The join solution is often faster. Make sure you have an index on (thread,mydate).

Answer 3

Ollie's answer worked, might try out Bill's too. Thanks guys, brilliant work.