CODEWITHSUNDEEP
sql-servert-sqlaggregate-functions
rahulthewall
rahulthewall

Reputation: 795

How to achieve MAX(COUNT(columnName))

I have the following table columns:

CommunicationNumber, DayOfWeekSlot, TwoHourSlot

I want to count the number of rows for each CommunicationNumber where DayOfWeekSlot and TwoHourSlot are the same. From this result I want to select the row where the count of (DayOfWeekSlot/TwoHourSlot) is the highest.

I wrote the following query:

SELECT tblLog.CommunicationNumber, MAX(tblLog.Priority) AS Frequency, tblLog.DayOfWeekSlot, tblLog.TwoHourSlot
FROM (SELECT CommunicationNumber, Count(*) AS Priority, DayOfWeekSlot, TwoHourSlot 
        FROM Miafon.dbo.tblPhoneLogRep
        WHERE CallDuration > 0
        GROUP BY CommunicationNumber, DayOfWeekSlot, TwoHourSlot) tblLog
GROUP BY tblLog.CommunicationNumber, tblLog.DayOfWeekSlot, tblLog.TwoHourSlot
ORDER BY tblLog.CommunicationNumber, Frequency DESC

The above query gives me the following columns:

CommunicationNumber, Frequency, DayOfWeekSlot, TwoHourSlot

Since I group by CommunicationNumber, TwoHourSlot and DayOfWeekSlot, I get all rows where these values differ (sorted in decreasing order of frequency). I only want the row with the highest value of frequency.

How do I achieve that?

Upvotes: 1

Views: 69

Answers (1)

sagi
sagi

Reputation: 40491

You can use row_number for that matter like this:

SELECT * FROM (
    SELECT tblLog.CommunicationNumber, tblLog.Priority AS Frequency, tblLog.DayOfWeekSlot, tblLog.TwoHourSlot
    ,row_number() OVER (PARTITION BY tblLog.CommunicationNumber order by tblLog.Priority desc) as rnk
    FROM (SELECT CommunicationNumber, Count(*) AS Priority, DayOfWeekSlot, TwoHourSlot 
          FROM Miafon.dbo.tblPhoneLogRep
          WHERE CallDuration > 0
          GROUP BY CommunicationNumber, DayOfWeekSlot, TwoHourSlot) tblLog)
WHERE rnk = 1

Upvotes: 2

Related Questions