SQL select top counts for grouped rows

group-by, sql, sql-server

Solution

My similar solution as @TechDo, but with 1 subquery

SELECT frequency,label,code FROM
(
  SELECT
    count(*) AS frequency
    ,MAX(COUNT(*)) OVER (PARTITION BY label) AS Rnk
    ,label
    ,code
  FROM myTable
  GROUP BY label, code
) x
WHERE frequency=Rnk
ORDER BY frequency DESC

SQLFiddle here

Problem

I have a table that has labels and some codes next to them: ``` id | label | code 1 | foo | 21 2 | foo | 33 3 | foo | 33 4 | foo | 13 5 | foo | 13 6 | foo | 33 7 | bar | 13 8 | bar | 13 9 | bar | 33 10 | smt | 33 11 | smt | 13 ``` I would need a query that selects top frequencies of the 'code' for every 'label'. Here is what I have so far: ``` SELECT count(*) frequency, label, code FROM myTable GROUP BY label, code ``` This gives me: ``` frequency | label | code 1 | foo | 21 3 | foo | 33 2 | foo | 13 2 | bar | 13 1 | bar | 33 1 | smt | 33 1 | smt | 13 ``` What I would like though is : ``` frequency | label | code 3 | foo | 33 2 | bar | 13 1 | smt | 33 1 | smt | 13 ``` As you can see only top frequencies are selected for 'foo' and 'bar'. Since 'smt' does not have a max frequency as such (all are the same), all rows are included. I do not have an idea even where to start. Anyone can help? thanks. (I am using mssql by the way)

Original source