How to Efficiently Find the Last Record in Each Group in MySQL?

MySQL Efficient to get the last record of each group

In SQL, extracting the latest records of each group may be more tricky. The common scene is: there are multiple records in the table to share the same main key, and you need to extract the latest items of each primary key. This article provides an efficient and optimized solution.

Problem description

Consider the following Table:

Basic group query using will produce the following results: messages

Id	Name	Other_Columns
1	A	A_data_1
2	A	A_data_2
3	A	A_data_3
4	B	B_data_1
5	B	B_data_2
6	C	C_data_1

However, the expected output is the latest record of each group:

SELECT * FROM messages GROUP BY Name

Solution

Id	Name	Other_Columns
1	A	A_data_1
4	B	B_data_1
6	C	C_data_1

Use the window function (MySQL 8.0 and higher versions)

Id	Name	Other_Columns
3	A	A_data_3
5	B	B_data_2
6	C	C_data_1

MySQL 8.0 introduced the window function to provide an elegant solution for finding the last record of each group. The following query uses function to achieve this purpose:

Lept join (early version of MySQL)

Before MySQL 8.0, the most effective solution is to use

: ROW_NUMBER()

WITH ranked_messages AS (
  SELECT m.*, ROW_NUMBER() OVER (PARTITION BY name ORDER BY id DESC) AS rn
  FROM messages AS m
)
SELECT * FROM ranked_messages WHERE rn = 1;

<能> Performance and applicability

Although both solutions are very efficient, their performance may vary from the size and distribution of data. For large and diverse datasets, the window function method is usually recommended.

If the data presents a specific mode (such as the relatively few records of each group), the LEFT JOIN method may be more efficient.

SELECT m1.*
FROM messages m1 LEFT JOIN messages m2
ON (m1.name = m2.name AND m1.id < m2.id)
WHERE m2.id IS NULL;

benchmark test

In many cases,

Methods have proven to be better than grouping technology. For example, in a large database with millions of lines, the execution time of the

method is less than one second, and the grouping technology takes more than one minute.

However, it is always recommended to test these two solutions on your specific data set to determine the best method. LEFT JOIN

The above is the detailed content of How to Efficiently Find the Last Record in Each Group in MySQL?. For more information, please follow other related articles on the PHP Chinese website!