About the processing of the newest and largest data in the grouping.

Today, a cold fever belly ache, a sickness all pull attack me. I am a little overwhelmed, even the brain is not an inspiration, incredibly was a very simple problem, trapped for a long time. After looking for information, incredibly to this question, and know profoundly a bit.

Problem:

Find the most recent and largest data in a grouped array:

Table structure
Choose| CREATE TABLE' choose ' (' Choose_no ' )int( One) not NULLauto_increment, ' Student_no 'Char( One) not NULL, ' Course_no 'int( One) not NULL, ' score 'tinyint(3) unsignedDEFAULT NULL, ' Choose_time 'datetime  not NULL,  PRIMARY KEY(' Choose_no '),KEY' choose_student_fk ' (' student_no '),KEY' choose_course_fk ' (' course_no '),CONSTRAINT' CHOOSE_COURSE_FK 'FOREIGN KEY(' Course_no ')REFERENCES' Course ' (' course_no ') on DELETE CASCADE,  CONSTRAINT' CHOOSE_STUDENT_FK 'FOREIGN KEY(' Student_no ')REFERENCES' student ' (' student_no ')) ENGINE=InnoDB auto_increment= - DEFAULTCHARSET=Utf8|

Insert  intoChooseValues(NULL,'2012001',2, +,'2015-09-20 11:32:59'),(NULL,'2012001',1, -,'2015-09-20 11:33:59'),(NULL,'2012002',3, -,'2015-09-20 11:34:59'),(NULL,'2012002',2, -,'2015-09-20 11:35:59'),(NULL,'2012003',1, the,'2015-09-20 11:36:59'),(NULL,'2012004',2, -,'2015-09-20 11:37:59'),(NULL,'2012005',3,NULL,'2015-09-20 11:38:59'),(NULL,'2012005',1,NULL,'2015-09-20 11:39:59');

1, in accordance with the Course_no (course number) group, for each course performance of the best person.

Select choose_no,student_no,course_no,Max(score) from choose Group by Course_no;

The simplest method, which best fits my logic, is wrong. The reason is that the maximum value of each grouping is right, but it does not correspond with the specific choose_no and student_no.

2. The second method:

Select  from (select choose_no,student_no,course_no,score score   from Order  by desc  as tmptable Group  by Course_no;

The second one solves the problem perfectly, but it is too troublesome to use a subquery to create a temporary table. Efficiency is not high. Need to be indexed.

About the processing of the newest and largest data in the grouping.