mysql max count within groups

Question

I have a table as follows

id  qNum  opNum
1   1     3
2   1     3
3   1     2
4   1     1
5   2     4
6   2     4
7   2     4
8   2     1

I need to get the count grouped by qNum and then opNum, and this is fine to do, but I only want to return the qNum and opNum of the records with the max count in each grouping

So I ideally need a recordset that looks like

qNum  opNum  maxCount
1     3      2
2     4      3

Help would be greatly appreciated!

Answer 1

While this doesn't look pretty, I think it achieves your desired results:

select y.qnum, y.opnum, maxcnt
from (
    select qnum, opnum, count(*) cnt
    from yourtable
    group by qnum, opnum
  ) y
  join (
    select qnum, max(cnt) maxcnt
    from (
      select qnum, opnum, count(*) cnt
      from yourtable
      group by qnum, opnum) t
    group by qnum
    ) t on y.qnum = t.qnum and y.cnt = t.maxcnt

• SQL Fiddle Demo