Group Top N records

I often see problems, how to retrieve the first N records of each group. For your convenience, I will list several common solutions.

Problem: The table is as follows. The first two names of each class must be taken out (the second name can be used together)
Table1
+ ---- + ------ + ----- +
| ID | sname | clsno | score |
+ ---- + ------ + ----- +
| 1 | AAAA | C1 | 67 |
| 2 | BBBB | C1 | 55 |
| 3 | CCCC | C1 | 67 |
| 4 | dddd | C1 | 65 |
| 5 | eeee | C1 | 95 |
| 6 | FFFF | C2 | 57 |
| 7 | GGGG | C2 | 87 |
| 8 | hhhh | C2 | 74 |
| 9 | iiii | C2 | 52 |
| 10 | jjjj | C2 | 81 |
| 11 | KKKK | C2 | 67 |
| 12 | llll | C2 | 66 |
| 13 | mmmm | C2 | 63 |
| 14 | NNNN | C3 | 99 |
| 15 | oooo | C3 | 50 |
| 16 | PPPP | C3 | 59 |
| 17 | qqqq | C3 | 66 |
| 18 | RRRR | C3 | 76 |
| 19 | ssss | C3 | 50 |
| 20 | tttt | C3 | 50 |
| 21 | UUUU | C3 | 64 |
| 22 | vvvv | C3 | 74 |
+ ---- + ------ + ----- +
The result is as follows:
+ ---- + ------ + ----- +
| ID | sname | clsno | score |
+ ---- + ------ + ----- +
| 5 | eeee | C1 | 95 |
| 1 | AAAA | C1 | 67 |
| 3 | CCCC | C1 | 67 |
| 7 | GGGG | c2 | 87 |
| 10 | jjjj | C2 | 81 |
| 14 | NNNN | C3 | 99 |
| 18 | RRRR | C3 | 76 |
+ ---- + ------ + ----- +

Method 1:
SelectA. ID, A. sname, A. clsno, A. Score
FromTable1Left
JoinTable1 BOnA. clsno = B. clsno
AndA. score <B. Score
Group ByA. ID, A. sname, A. clsno, A. Score
Having Count(B. ID) <2
Order ByA. clsno, A. Score
Desc

Method 2:

Select*
FromTable1
Where2> (Select
Count(*)FromTable1
WhereClsno = A. clsno
AndScore> A. Score)
Order ByA. clsno, A. Score
Desc

 

Method 3:
Select*
FromTable1
WhereIDIn(SelectID
FromTable1WhereClsno = A. clsno
Order ByScore
Desc limit 2)
Order ByA. clsno, A. Score
Desc

 

Method ....

Here lists the implementation of a variety of SQL statements, some are unique to MySQL (limit, other databases can be changed according to the actual, such as Oracle rownum, ms SQL Server top ,..), sometimes it is supported by SQL standards. However, the efficiency may be different from the application scenarios. You can select an index based on the actual table record.

 

 

Special case n = 1, that is, take the largest/smallest record.
+ ---- + ------ + ----- +
| ID | sname | clsno | score |
+ ---- + ------ + ----- +
| 5 | eeee | C1 | 95 |
| 7 | GGGG | C2 | 87 |
| 14 | NNNN | C3 | 99 |
+ ---- + ------ + ----- +

Select *
From Table1
Where not exists (select 1 from Table1 where clsno = A. clsno and score> A. Score );

 

 

Select .*
From Table1 a inner join (select clsno, max (score) as mscore from Table1 group by clsno) B
On a. clsno = B. clsno and A. Score = B. Score

Select *
From (select * From Table1 order by score DESC) T
Group by clsno