CODEWITHSUNDEEP
mysqlgroup-by
Frank
Frank

Reputation: 1864

SQL GROUP BY selecting

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

enter image description here

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

enter image description here

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?

Upvotes: 2

Views: 224

Answers (3)

Jason McCreary
Jason McCreary

Reputation: 72961

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.

Upvotes: 6

Dipu Raj
Dipu Raj

Reputation: 1884

Try with this 

SELECT *,MAX(id) as OrderField FROM messages WHERE to_user_id = '$user_id' 
 GROUP BY from_user_id 
 ORDER BY OrderField DESC,read

Upvotes: 1

Code Magician
Code Magician

Reputation: 23972

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.

Upvotes: 2

Related Questions