Implement group by based on MySQL to get the latest data of each group

Preface:

The group by function retrieves the first piece of data in the group, but sometimes we need to retrieve the latest piece of data in each group. How can we achieve this?

This article provides two implementation methods.

1. Prepare data

http://note.youdao.com/noteshare?id=dba748092a619be0a8f160ccf6e25a5f&sub=FD4C1C7823CA440DB360FEA3B4A905CD

Two, three implementation methods

1) Order by first and then group:

SELECT * FROM (SELECT * from tb_dept ORDER BY id descLIMIT 10000) a GROUP BY parent_id;

Not adding LIMIT may be invalid due to MySQL version issues. But I always feel that this way of writing is not very serious, because if the amount of data is greater than the Limit value, the result will be inaccurate. So there is a second way of writing.

2) Using the max() function:

SELECT * FROM tb_dept td,(SELECT max(id) id FROM tb_dept GROUP BY parent_id) md where td.id = md.id;

3) Use the where field name in (...) function:

SELECT * FROM tb_dept WHERE id IN (SELECT MAX(id) FROM tb_dept GROUP BY parent_id);

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.