CODEWITHSUNDEEP
sqlsql-servergroup-by
Daniel Gruszczyk
Daniel Gruszczyk

Reputation: 5622

SQL select top counts for grouped rows

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)

Upvotes: 4

Views: 3874

Answers (3)

NickyvV
NickyvV

Reputation: 1744

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

Upvotes: 2

molnarm
molnarm

Reputation: 10051

Using your query and RANK():

SELECT frequency, label, code FROM
(
    SELECT frequency, label, code, RANK() OVER(PARTITION BY code ORDER BY frequency DESC) [rank]
    FROM (
        SELECT count(*) frequency, label, code
        FROM myTable
        GROUP BY label, code
    ) Counts
) Ranked
WHERE [rank] = 1
ORDER BY frequency DESC

Upvotes: 0

TechDo
TechDo

Reputation: 18659

Please try:

SELECT * FROM(
    SELECT *, 
        MAX(frequency) OVER(PARTITION BY label) Col1
    FROM(
        SELECT count(*) frequency, label, code
        FROM myTable
        GROUP BY label, code
    )x
)xx 
WHERE frequency=Col1

Upvotes: 2

Related Questions