CODEWITHSUNDEEP
mysql
Dave
Dave

Reputation: 387

mysql max count within groups

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!

Upvotes: 0

Views: 105

Answers (2)

Sławomir Pawluk
Sławomir Pawluk

Reputation: 106

Simpler solution might be:

SELECT qNum, opNum, MAX(temp.maxcount) AS maxcount FROM(
SELECT qNum, opNum, COUNT(*) AS maxCount
FROM t
GROUP BY opNum, qNum ORDER BY maxcount DESC
) AS temp GROUP BY qNum

Upvotes: 1

sgeddes
sgeddes

Reputation: 62841

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

Upvotes: 1

Related Questions