CODEWITHSUNDEEP
sql-server
Simhadri
Simhadri

Reputation: 931

Query to get top maximum value order by 2 columns?

I need to get maximum count of rows based on 2 column names.

I have table data like below

Num     Pack   SubPack
98989   Hindi   H1
1234    Hindi   H2
1111    Hindi   H2
11213   English E1
13252   English E2
8664    ENglish E1
32141   Hindi   H2

I need to get output like below

PackName SubPackName Count

Hindi       H2        3
English     E1        2

Below is the query I have used 

With Q As(Select Count(*) as MobNos,PackName,SUbpackname
From tblCaller
Group By PackName,SUbpackname )
Select Packname,SUbPackname,Max(MobNos) as MaxCount From Q
Group By PackName,SUbpackname
Order By MaxCount Desc

Upvotes: 2

Views: 631

Answers (2)

jwd
jwd

Reputation: 11114

I tried this in SQLite, I'm sure you can modify it for SQLServer.

It's a little gross since it has duplicated subqueries. You could use a 'WITH' clause, view, or temporary table to avoid that.

Note: this is doing essentially the same work as AJ01's answer.

SELECT
    SubPackCounts.*
FROM
    -- Make a subquery with counts grouped by subpacks
    (SELECT
        PackName,
        SubPack,
        COUNT(SubPack) AS Count
    FROM
        tblCaller
    GROUP BY SubPack
    ) AS SubPackCounts
    ,
    -- Make a subquery that has PackName and the associated max subpack count
    (SELECT
        PackName,
        MAX(Count) AS MaxCount
    FROM
            -- Note: this is the same sub-query as above
        (SELECT
            PackName,
            SubPack,
            COUNT(SubPack) AS Count
        FROM
            tblCaller
        GROUP BY SubPack
        ) AS SubPackCounts
    GROUP BY PackName
    ) AS PackMax
WHERE
    -- Only show results that are max
    SubPackCounts.Count = PackMax.MaxCount
GROUP BY PackName   -- Remove duplicates in the case of ties

Upvotes: 0

ajk
ajk

Reputation: 4603

You should be able to do this by joining Q to itself and only displaying rows that have the maximum count for a given pack/subpack combination. There is likely a more elegant way to do this, but give this a shot until you find it!

With Q As(
  Select Count(*) as MobNos,PackName,SubPackName
  From tblCaller
  Group By PackName,SubPackName
)
select q1.PackName,q2.SubPackName,MAX(q1.MobNos) 'Total'
from
    Q q1
    join Q q2 on q1.PackName = q2.PackName
group by q1.PackName, q2.SubPackName, q2.MobNos
having q2.MobNos = MAX(q1.mobnos)
order by total desc

Upvotes: 1

Related Questions