Mysql uses group by to obtain the first N records. mysqlgroup

Mysql uses group by to obtain the first N records. mysqlgroup

This example describes how mysql uses group by to retrieve the first N records. We will share this with you for your reference. The details are as follows:

Implement group by grouping of the First N records in MySQL

Mysql group, retrieve records

The following describes how to obtain the first two records of each GROUP after group by in mysql.

This is a test table (I don't know how to think about it. At that time, the table name was directly typed into aa, Khan ~~~~) :

Result:

Method 1:
Copy codeThe Code is as follows: SELECT. id,. SName,. clsNo,. score FROM aa a left join aa B ON. clsNo = B. clsNo AND. score <B. score group by. id,. SName,. clsNo,. score having count (B. id) <2 order by. clsNo,. score desc;

Split analysis:

<! -- [If! SupportLists] --> 1. <! -- [Endif] --> left join aa B ON a. ClsNo = B. ClsNo AND a. Score <B. Score

In the same class (four persons in each class), the score is higher than the current student's record, which means that the students with the lowest score will generate three records

<! -- [If! SupportLists] --> 2. <! -- [Endif] --> group by a. id, a. SName, a. ClsNo, a. Score having count (B. id) <2
A. id,. SName,. clsNo,. score can represent a student (grouped by student). If count (B. id) <2 (no more than two people have scored more than you), then only the first and second places are left.

Method 2:
Copy codeThe Code is as follows: SELECT * FROM aa a WHERE 2> (select count (*) FROM aa WHERE ClsNo =. clsNo and Score>. score) order by. clsNo,. score DESC;
I think this is quite interesting. Take every record and judge whether the number of students in the same class is less than two if they exceed the current score.

Method 3:
Copy codeThe Code is as follows: SELECT * FROM aa WHERE id IN (SELECT id FROM aa WHERE ClsNo = a. ClsNo order by Score desc limit 2) order by a. ClsNo, a. Score DESC;
This method fails the test. ERROR 1235 (42000): This version of MySQL doesn't yet support 'limit & IN/ALL/ANY/SOME subquery ', limit cannot be used in these subqueries.