Depth Analysis Mysql GROUP by and order By_mysql

This article and everyone together in-depth research under MySQL group BY and order by. Here's my demo of my content table

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

Write Order: Select ... from ... where ... the group by ... having ... the.??
Order of execution: From ... where...group by ... having ... a select .....

So the result of the order by is already the end result of the grouping.
The results from the from to where are as follows.

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

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

Even if order by is only sorted from the result above. Not the most recent information for each category.
Back to my purpose--the latest information in the category
According to the analysis above, group by to select only takes the first piece of information in the group. There are two ways to solve this problem

1,where+group by (sort the group)
2, the data returned from the form of the hands and feet (that is, subqueries)
The solution by the Where+group by
A function that sorts the groups in group by I only find Group_concat () can sort, 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 (select Substring_index (Group_concat (ID order by ' date ' desc), ', ', ', 1) ' Test ' GROUP by category_id) Order BY ' date ' desc

Subquery Solution

SELECT * FROM (SELECT * "Test ' ORDER by '" Date ' desc ') ' temp ' GROUP by category_id Order by ' date ' desc

own example

Copy Code code as follows:

SELECT * FROM (SELECT * from ' Ecm_copy_goods_change ', ' CID ' DESC) ' Ecm_copy_goods_change ' GROUP by goods_id Order By ' CID ' DESC