Mysql & quot; groupby & quot; and & quot; orderby & q_MySQL

Mysqlamp; quot; groupbyamp; quot; and amp; quot; orderbyamp; quot; research-the latest content in the classification bitsCN.com

Research on mysql "group by" and "order by" -- the latest content in classification

These two days make it difficult to query a data. The main reason is that the understanding of group by is not deep enough. This is the case.
I think many people have met this requirement. Below is my content table simulation
The code is as follows:

Create table 'test '(
'Id' INT (10) not null AUTO_INCREMENT,
'Name' VARCHAR (255) not null,
'Category _ id' INT (10) not null,
'Date' timestamp not null default CURRENT_TIMESTAMP,
Primary key ('id ')
)
ENGINE = MyISAM
ROW_FORMAT = DEFAULT;
Insert into 'test' ('id', 'name', 'Category _ id', 'date ')
VALUES
(1, 'AAA', 1, '2017-06-10 19:14:37 '),
(2, 'BBB ', 2, '2017-06-10 19:14:55 '),
(3, 'CCC ', 1, '2017-06-10 19:16:02 '),
(4, 'ddd ', 1, '2017-06-10 19:16:15 '),
(5, 'Eee ', 2, '2017-06-10 19:16:35 ');


I need to retrieve the latest content in each category.

The code is as follows:

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

Reference

Write order: select... from... where... group by... having... order ..
Execution sequence: from... where... group by... 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. Not the latest information for each category.
Back 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 form has the following hands and feet (I .e., subquery)

Solution by where + group
I only find that group_concat () can be used to sort groups in group by, but group_concat is used to concatenate values in fields in the group.
Select group_concat (id order by 'date' desc) from 'test' group by category_id


Try again
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

BitsCN.com