MySQL "GROUP by" and "Order by" research--the newest content in the classification

These two days make a data query difficult. The main focus is not on group by understanding. That's what happened.
This demand, I think a lot of people have met. Here's what I'm simulating my table of contents

I need to remove the latest content from each category now

SELECT * FROM test group by category_id order by ' date '

The results are as follows

Obvious. This is not the data I want, because the order in which MSYQL has been executed is

Reference
The Order of writing: Select ... from ... where ... a group by ... have ... order by ...
Execution order: From ... Where...group by ... having .... Select ... order by ...

So the result in order by is already the end result of the grouping.
The result from the from to where is the following.

By the time group BY, we got a number of groups based on category_id.


When you get to select, only the first message from each of the groups above will result in the following

Even if order by is only sorted from the results above. Not the latest information for each category.
Back to my purpose-the latest information in the category
Based on the above analysis, group by to select only takes the first piece of information in the group. There are two workarounds
1,where+group by (sort the group)
2, the data returned from the form of the hands and feet (that is, with subqueries)

Workaround by Where+group
Functions for sorting groups by group I only found that group_concat () can be sorted, but Group_concat's role is to concatenate the values in the fields in the group.

Select Group_concat (ID order by ' date ' desc) from ' Test ' GROUP by category_id

Just a little bit better.

SELECT * from ' test ' where ID in (the Select Substring_index (Group_concat (ID order by ' date ' desc), ', ', 1) from ' Test ' GROUP by category_id) Order BY ' date ' desc

Sub-query Solution

SELECT * FROM (SELECT * from ' Test ' order by ' date ' desc) ' temp ' GROUP by category_id Order by ' date ' desc

MySQL "GROUP by" and "Order by" research--the newest content in the classification