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

How to modify port 3389 of Windows server 2008 R2 remote desktop

The default port number of the Windows server rem...

Realizing tree-shaped secondary tables based on angular

First look at the effect: Code: 1.html <div cl...

A brief discussion on several situations where MySQL returns Boolean types

mysql returns Boolean type In the first case, ret...

CSS realizes the layout method of fixed left and adaptive right

1. Floating layout 1. Let the fixed width div flo...

Correct use of Vue function anti-shake and throttling

Preface 1. Debounce: After a high-frequency event...

The meaning of the 5 types of spaces in HTML

HTML provides five space entities with different ...

Detailed steps for installing and configuring MySQL 8.0 on CentOS

Preface Here are the steps to install and configu...

Getting Started: A brief introduction to HTML's basic tags and attributes

HTML is made up of tags and attributes, which are...

MySQL Quick Data Comparison Techniques

In MySQL operation and maintenance, a R&D col...

Summary of frequently used commands for Linux file operations

0. New operation: mkdir abc #Create a new folder ...

JavaScript Reflection Learning Tips

Table of contents 1. Introduction 2. Interface 3....

Detailed explanation of MySQL instance with SSD storage enabled

Detailed explanation of MySQL instance with SSD s...

Distributed monitoring system Zabbix uses SNMP and JMX channels to collect data

In the previous article, we learned about the pas...

Summary of essential Docker commands for developers

Table of contents Introduction to Docker Docker e...

Things to note when writing self-closing XHTML tags

The img tag in XHTML is so-called self-closing, w...