SQL GROUP BY selecting

Question

Here is my code:

mysql_query("SELECT * FROM messages WHERE to_user_id = '$user_id' GROUP BY from_user_id ORDER BY read,sent_date DESC")

and here is my table structure

I use the GROUPY BY from_user_id statement to briefly show a list of "conversations" instead of every single message. Like this

But, as you can see in the image, the top two are the wrong way round, the first one says "1 week ago" and the one below says "2 days ago". The reason for these being in the wrong order is due to the GROUP BY from_user_id statement. Because it groups all the messages from that user and it wont have the the most recent time on it.

So my question is:

How can I GROUP BY from_user_id by the most recent record?

Answer 1

You can not SELECT columns not listed in the GROUP BY or otherwise allowed functions. This is how GROUP BY works.

Although most other SQL flavors would fail on such a query, MySQL doesn't. Instead it provides arbitrary results - what you are seeing.

You can solve this a few different ways. For example:

• SELECT the ids of the latest user conversations using GROUP BY in a separate query.
• Do it all in one query by sub-selecting the ids or JOIN the set of ids.

Answer 2

Since MySQL doesn't support windowing functions like ROW_NUMBER() OVER() you can do something like this:

SELECT * 
FROM Messages 
where id in (SELECT MAX(ID) FROM messages GROUP BY from_user_id) 
ORDER BY sent_date, read 

The subquery will only return the newest message id for each user. I'm assuming your auto_increment corresponds with the order the messages are sent in. Even if it's not the exact logic you might want, this is a general technique to get a specific subset of values from grouped records that works in MySQL.