How to sort and group mysql group by first

This tutorial will introduce a common problem related to my friends who are troubled by mysql database tutorials. mysql group by uses both sorting and grouping methods. Let's look at the example below.

View sorting

Select * from (select * from posts order by dateline desc) group by tid order by dateline desc limit 10

The efficiency should be high. Cancels the filesort process of order.

Select * from t where logtime in (select max (logtime) from t group by username)

 

Some netizens also use self-connection to achieve higher efficiency than the preceding subquery. However, to be simple and clear, only such a method is used. group by has no sorting function, maybe it's something that mysql is mentally retarded, maybe it's something I haven't found

--
-- Table structure 'test'
--

Create table if not exists 'test '(
'Id' int (11) not null auto_increment,
'Name' varchar (16) not null,
'Month' int (11) not null,
'Serial' int (11) not null,
'Other' varchar (20) not null,
Primary key ('id ')
) Engine = myisam default charset = utf8;

--
-- Export the table data 'test'
--

Insert into 'test' ('id', 'name', 'month', 'serial', 'other') values
(1, 'A', 200807, 2, 'aaa1 '),
(2, 'A', 200805, 2, 'aaa2 '),
(3, 'B', 200805, 3, 'bbb3 '),
(4, 'B', 200805, 4, 'bbb4 '),
(5, 'A', 200805, 1, 'aaa5 '),
(6, 'C', 200807, 5, 'ccc6 '),
(7, 'B', 200807, 8, 'bbb7 '),
(8, 'C', 200807, 3, 'ccc8 '),
(9, 'A', 200805, 6, 'aaa9 ');

Query
Select * from (select * from test order by month desc, serial desc) t group by name
Get
Id name month serial other
1 a 200807 2 aaa1
7 B 200807 8 bbb7
6 c 200807 5 ccc6
Change the sorting method
Select * from (select * from test order by month asc, serial desc) t group by name
Get
Id name month serial other
9 a 200805 6 aaa9
Four B 200805 4 bbb4
6 c 200807 5 ccc6
The results are displayed as per our requirements.