Sort mysql group by to obtain the maximum time record of each record.

This article introduces how to use group by sorting in mysql to obtain the most recent records in a record group. For more information, see.

Mysql group by: sorts the records with the largest time in each record.

Test SQL

Create table if not exists 'test '(
'Id' int (10) unsigned not null auto_increment,
'Install' int (10) unsigned not null,
'Day' int (10) unsigned not null,
'Aid 'int (10) unsigned not null,
Primary key ('id ')
) ENGINE = MyISAM default charset = utf8 AUTO_INCREMENT = 12;

Insert into 'test' ('id', 'install', 'day', 'aid ') VALUES
(1, 1232,200, 1 ),
(2, 2321,200 80809, 2 ),
(3, 1236,200 80810, 3 ),
(5, 4212,200 80809, 1 ),
(6, 2312,200 80810, 1 ),
(7, 1432,200, 1 ),
(8, 2421,200, 2 ),
(9, 4245,200 80811, 2 ),
(10,565 4, 20080810, 2 ),
(11,412,200, 3 );

SQL statement

The Code is as follows:	Copy code
Select a. * FROM test, (SELECT aid, MAX (day) max_day FROM test group by aid) B Where a. aid = B. aid and a. day = B. max_day Order by a. install DESC

We can also use group by for instance record statistics.

Method 1: Use GROUP

The Code is as follows:	Copy code
SQL Query Select count (*) FROM 'Prince' group by 'Mother '> 24;

Execution result

Count (*)
50029
49971
Running time on 100,000 rows of data: 0.0335 seconds

Analysis
The biggest problem with this group by method is that the results cannot be distinguished. Which of the two figures is the number of emperors generated by the mother-in-law of the tiangu, and which one is the number of emperors generated by the mother-in-law of the tiangu? I don't know. Therefore, although it counts the total number, it makes no sense.

Therefore, in order to differentiate statistical results, the condition mother> 24 must also be used as a field in the result set as a field. The modified SQL statement is as follows:

The Code is as follows:	Copy code
Select count (*) AS 'number', 'Mother '> 24 AS 'type' FROM 'Prince 'group BY 'Mother'> 24;

Execution result

Number type
50029 0
49971 1

When a condition expression is used as a field, the value of this field is the value of this condition expression. Therefore, in our example, type = 1 indicates that the value of mother> 24 is 1. Therefore, the numbers in the second row represent the number of emperors of the mother-in-law.

After modification, we can see that tiangu Niang is better than others.