Detailed usage of count (), group by, and order by in mysql, and detailed usage of mysql

Detailed usage of count (), group by, and order by in mysql, and detailed usage of mysql

I encountered a problem when I was doing IM recently and used these three keywords at the same time. This is to query the offline message details of a person. The details returned by the server to the client include three items. The first requirement is to list who sent messages to you during the offline period, the second one shows the number of offline messages sent by one person or a group, and the third one displays the latest one. Obviously, the group by group has sent you offline messages. count () gets the number of offline messages and order by times to sort 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, if group by and order by are searched together, an error is returned. 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 sorted result sets. There is also a dark hole here. I accidentally hid it. In fact, count () will lead to invalid order by sorting. 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 last record obtained by this statement, in which msg_data is actually not sorted, that is, the original database order, should be a message inserted first, that is, the old time message. To avoid this problem, nested subqueries are sorted first here, so that they can be circumvented by going to count. I did not intend to hide it, but I was able to find it only after I tried it again and again.