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);
8 rows affected
with cte as
(
select username,class,count(*) total,
row_number()over (partition by class order by count(*) desc) rn
from signin
group by class,username
)
select username,class,total from cte where rn<=2
| username | class | total |
|---|---|---|
| aaa | 1 | 2 |
| bbb | 1 | 2 |
| ddd | 2 | 2 |
| eee | 2 | 1 |