Detailed explanation of count(), group by, order by in MySQL

I recently encountered a problem when doing IM, and used these three keywords at the same time. It is to query the offline message details of a person. The details returned by our server to the client include three contents. The first one requires to list which people or groups have sent you messages during the offline period. The second one is how many offline messages a certain person or group has sent. The third one is to display the latest one. Obviously, group by groups which people or groups have sent you offline messages, count() gets the number of offline messages, and order by time sorts to get the latest messages.

select count(1) as cnt, msg_data from t_im_chat_offline_msg where to_company_id = ? and to_user_id = ? order by create_time desc group by from_company_id, from_user_id;

Then, as expected, an error occurs when searching with group by and order by together. We can use nested subqueries.

select count(1) as cnt, msg_data from (select * from t_im_chat_offline_msg where to_company_id = ? and to_user_id = ? order by create_time desc) as temp_table group by from_company_id, from_user_id;


We can group and calculate the number of results that have already been sorted. There is a hidden pit here, which I accidentally avoided. In fact, count() will cause the order by sort to be invalid. For example:

select count(1) as cnt, msg_data from t_im_chat_offline_msg where to_company_id = ? and to_user_id = ? order by create_time desc;

The statement finally obtains a record in which msg_data is actually not sorted at all, that is, the original order of the database. It should be the message that was inserted first, that is, the old message. To avoid this problem, the nested subquery is sorted first and then count() is used to avoid it. I dodged it unintentionally, but I discovered it after I modified the sentence and tested it again and again.