mysql how to get the latest data/last data in the group (group by)

created at 07-26-2021 views: 2

Data

There is a data table for targt, the data is as follows:

Id   Name   Other_Columns
-------------------------
1    A       1
2    A       2
3    A       3
4    B       4
5    B       5
6    C       6

scenes to be used

I now want to extract the last data of the three groups named A, B, and C. Use the group by statement: select * from target group by name to get the following results

Id   Name   Other_Columns
-------------------------
1    A       1
4    B       4
6    C       6

How to efficiently obtain the following results?

Id   Name   Other_Columns
-------------------------
3    A       3
5    B       5
6    C       6

solution

The first method: mysql8.0 supports window function

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

The second method Multi-level nested query:

select * from `target` 
where `id` in 
(select max(`id`) from `target` group by `name`)

More optimized

select b.* from
(select max(`id`) as id from `target` group by `name`) as a
join `target`as b on
a.id=b.id

The third method The best method:

SELECT * FROM `target ` GROUP BY name DESC
Please log in to leave a comment.