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

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.

You may also be interested in:
  • Usage and performance optimization techniques of aggregate function count in MySQL
  • MySQL count detailed explanation and function example code
  • Detailed explanation of the usage of the COUNT function in MySQL
  • A brief discussion on MySQL count of rows
  • Summary of methods to improve mysql count
  • Detailed explanation of count without filter conditions in MySQL
  • Summary of the differences between count(*), count(1) and count(col) in MySQL
  • Comparison of usage of distinct and count(*) in MySQL
  • A convenient way to count the total number of rows in MYSQL query results without counting (*)
  • Explanation and usage of found_row() and row_count() in MySQL
  • Detailed explanation of the correct use of the count function in MySQL

<<:  WeChat applet to achieve the revolving lantern effect example

>>:  A complete example of Vue's multi-level jump (page drill-down) function for related pages

Recommend

Native js implements a minesweeper game with custom difficulty

This article example shares the specific code of ...

How to calculate the frame rate FPS of web animations

Table of contents Standards for smooth animation ...

Analysis of log files in the tomcat logs directory (summary)

Each time tomcat is started, the following log fi...

Examples of using Docker and Docker-Compose

Docker is an open source container engine that he...

Complete steps to quickly build a vue3.0 project

Table of contents 1. We must ensure that the vue/...

One line of code solves various IE compatibility issues (IE6-IE10)

x-ua-compatible is used to specify the model for ...

Case analysis of several MySQL update operations

Table of contents Case Study Update account balan...

Detailed explanation of the solution to Tomcat's 404 error

The 404 problem occurs in the Tomcat test. The pr...

Use Vue3+Vant component to implement App search history function (sample code)

I am currently developing a new app project. This...

Solve the problem of Docker starting Elasticsearch7.x and reporting an error

Using the Docker run command docker run -d -p 920...

Docker learning: the specific use of Container containers

Container is another core concept of Docker. Simp...

Example of CSS3 to achieve div sliding in and out from bottom to top

1. First, you need to use the target selector of ...