SQL: GROUP BY and get first two biggest COUNTs for each group

Question

Table: signin

---

username	class
aaa	1
aaa	1
bbb	1
bbb	1
ccc	1
ddd	2
ddd	2
eee	2

There are two classes. How do I get first TWO username who has the most total count for each class(class-1 & class-2) like below ↓.

username	class	total
aaa	1	2
bbb	1	2
ddd	2	2
eee	2	1

EDIT: Is there any way not using over partition by?

Answer 1

In older versions of MySQL, you would use variables. But you have to be careful with the syntax.

The basic idea is aggregation and then enumeration:

select uc.*
from (select uc.*,
             (@rn := if(@c = class, @rn + 1,
                        if(@c := class, 1, 1)
                       )
             ) as seqnum
      from (select username, class, count(*) as cnt
            from signin
            group by username, class
            order by class, cnt desc
           ) uc cross join
           (select @c := '', @rn := 0) params
     ) uc
where seqnum <= 2;

There are three very important things to remember when using variables in a select:

1. They are deprecated. In more recent versions, you should be using window functions for this type of operation.
2. MySQL does not guarantee the order of evaluation of expressions in a SELECT. So, you should handle all variables in a single expression, which is why the @rn := is rather complicated.
3. MySQL does not guarantee that the ORDER BY is evaluated before the variable assignments. That is why the ORDER BY is in the subquery.

Answer 2

With rank() ranking window function and common table expression you can easily achieve that.

To get the usernames with most count in a class I have used rank()over (partition by class order by count(*) desc) along with group by class,username

Schema and insert statements:

 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);

Query:

 with cte as
 (
     select username,class,count(*) total, 
     rank()over (partition by class order by count(*) desc) rn
     from signin
     group by class,username
 )
 select username,class,total from cte where rn<=2

Output:

username	class	total
aaa	1	2
bbb	1	2
ddd	2	2
eee	2	1

db<>fiddle here

Answer 3

In older version of MySQL you need to generate the ranking manually without any ranking function. Below code will work.

Schema and insert statements:

 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);

Query:

 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
              
      

Output:

username	class	total
aaa	1	2
bbb	1	2
ddd	2	2
eee	2	1

db<fiddle here

Answer 4

Rank them by count in descending order and display only the first two. Sample data in lines #1 - 10; query begins at line #11.

SQL> with signin (username, class) as
  2    (select 'aaa', 1 from dual union all
  3     select 'aaa', 1 from dual union all
  4     select 'bbb', 1 from dual union all
  5     select 'bbb', 1 from dual union all
  6     select 'ccc', 1 from dual union all
  7     select 'ddd', 2 from dual union all
  8     select 'ddd', 2 from dual union all
  9     select 'eee', 2 from dual
 10    )
 11  select username, class, total
 12  from (select username, class, count(*) total,
 13          rank() over (partition by class order by count(*) desc) rnk
 14        from signin
 15        group by username, class
 16       )
 17  where rnk <= 2
 18  order by class, username;

USE      CLASS      TOTAL
--- ---------- ----------
aaa          1          2
bbb          1          2
ddd          2          2
eee          2          1

SQL>