MySql order by problem

Question

Hello.

I want to list messages that received specific user from other users group by ID's and ordered by last message received.

If I use this query:

SELECT MAX(id), fromid, toid, message 
  FROM pro_messages 
 WHERE toid=00003 
GROUP BY fromid

I do not get last message sent from user "fromid" to user "toid" but the first message sent. Can I do that in some other way or I need to do it with two queries or join tables?

id - message id fromid - id of user who sent message toid - id of user who receive message (in this case user 00003)

Answer 1

First, you'll want to be careful with your toid value there. The zero-padding could cause trouble if it's interpreted as an octal number: 00010 is 9 decimal, and toid=9 is most likely not what you're intending.

As for your message trouble, you're getting normal behavior for the GROUP operator in MySQL. Consider the following simplified table:

mysql> select * from messages;
+------+--------+------+---------+
| id   | fromid | toid | message |
+------+--------+------+---------+
|    1 |    100 |    3 | hi      | 
|    2 |    100 |    3 | there   | 
|    3 |    100 |    3 | how     | 
|    4 |    101 |    3 | did     | 
|    5 |    100 |    3 | are you |  <--the message you want, right?
+------+--------+------+---------+

When you group on fromid, the database collapses all the rows matching the grouping conditions into a single row. In this case, your query will get the following results

mysql> select max(id), fromid, toid, message from messages where toid=3 group by fromid;
+---------+--------+------+---------+
| max(id) | fromid | toid | message |
+---------+--------+------+---------+
|       5 |    100 |    3 | hi      | 
|       4 |    101 |    3 | did     | 
+---------+--------+------+---------+

Notice now the '100' row has the max(id) of 5, but the message from id=1. This is how the GROUP BY operations work. You can't work around this with a simple query, but if you switch to a sub-select as follows, you'll get the proper results:

mysql> select id, fromid, toid, message from messages where id in (select max(id) from messages where toid=3 group by fromid);
+------+--------+------+---------+
| id   | fromid | toid | message |
+------+--------+------+---------+
|    4 |    101 |    3 | did     | 
|    5 |    100 |    3 | are you | 
+------+--------+------+---------+