MySQL GROUP by sort takes one of the most time records in each record

MySQL group BY, sorting the largest record of time in each record

Here is the test table, which tests the SQL

CREATE TABLE IF not EXISTS ' test ' (
' ID ' int (a) unsigned not NULL auto_increment,
' Install ' int (a) unsigned not NULL,
' Day ' int (a) unsigned not NULL,
' Aid ' int (a) unsigned not NULL,
PRIMARY KEY (' id ')
) Engine=myisam DEFAULT Charset=utf8 auto_increment=12;

INSERT into ' test ' (' ID ', ' Install ', ' Day ', ' aid ') VALUES
(1, 1232, 20080808, 1),
(2, 2321, 20080809, 2),
(3, 1236, 20080810, 3),
(5, 4212, 20080809, 1),
(6, 2312, 20080810, 1),
(7, 1432, 20080811, 1),
(8, 2421, 20080808, 2),
(9, 4245, 20080811, 2),
(10, 5654, 20080810, 2),
(11, 412, 20080808, 3);

SQL statement

The code is as follows	Copy Code
SELECT a.* from Test A, (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 to instance record statistics

Method 1: Use the GROUP by

The code is as follows	Copy Code
SQL Query SELECT COUNT (*) from ' Prince ' GROUP by ' mother ' > 24;

Execution results

COUNT (*)
50029
49971
Elapsed time on 100,000-line data: 0.0335 seconds

Analysis
The biggest problem with this group by method is that the resulting results cannot be distinguished. These two numbers which is a heavenly Palace empress the number of the Prince, which is the number of the Empress of the palace of the prince? I don't know. So, although it counts the total, it doesn't make any sense.

Therefore, in order to differentiate the statistical results, the conditions Mother > 24 must also be represented as a field in the result set as a field, and the modified SQL is as follows:

The code is as follows	Copy Code
SELECT COUNT (*) as ' number ', ' mother ' > as ' type ' from ' Prince ' GROUP by ' mother ' > 24;

Execution results

Number Type
50029 0
49971 1

When a conditional expression is a field, the value of that field is the value of the conditional expression, so, for our example, type = 1 means that the value of Mother > 24 is 1, so the number in the second line represents the number of the men born to the Empress of the palace.

After modification, we see that the heavenly empress slightly.