By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table signin(username varchar(50),class int);
insert into signin values('aaa', 1);
insert into signin values('aaa', 1);
insert into signin values('bbb', 1);
insert into signin values('bbb', 1);
insert into signin values('ccc', 1);
insert into signin values('ddd', 2);
insert into signin values('ddd', 2);
insert into signin values('eee', 2);
select username,class,total
from
(
select username,class,count(*) total ,
@rn := IF(@prev = class, @rn + 1, 1) AS rn,
@prev := class
from signin
JOIN (SELECT @prev := NULL, @rn := 0) AS vars
group by class,username
order by class,username,count(*) desc
)t
where rn<=2
| username | class | total |
|---|---|---|
| aaa | 1 | 2 |
| bbb | 1 | 2 |
| ddd | 2 | 2 |
| eee | 2 | 1 |