In-depth study of the latest time for group by and order by in mysql

This article will take a closer look at group by and order by in mysql. below is my content table simulation
Now I need to retrieve the latest content in each category. select * from test group by category_id order by 'date'. The result is as follows:
Obviously. This is not the data I want, because the execution sequence of msyql is the order of reference writing: select... from... where .... group... having... order .. execution sequence: from... where... group... having .... select... order... therefore, the result obtained by order by is the final result of grouping. The result from where to from is as follows.
At group by, multiple groups are obtained according to category_id.

When the select statement is adopted, the result is as follows:
Even order by is sorted from the above results. -Www.2cto.com-not the latest information for each category. Return to my goal-the latest information in the category. According to the above analysis, only the first information in the group is obtained when group by is selected. There are two solutions: 1. where + group by (sort groups) 2. The data returned from the form is under the hand (I .e. subquery)
The where + group by function sorts groups in group by. I only find that group_concat () can be sorted, however, group_concat is used to concatenate values in fields in a group. Select group_concat (id order by 'date' desc) from 'test' group by category_id


Next, let's improve the select * from 'test' where id in (select SUBSTRING_INDEX (group_concat (id order by 'date' desc), ',', 1) from 'test' group by category_id) order by 'date' desc
Subquery solution select * from (select * from 'test' order by 'date' desc) 'temp 'group by category_id order by 'date' desc
From Wang He's blog