CODEWITHSUNDEEP
sql-server-2008
Ofer Gozlan
Ofer Gozlan

Reputation: 1123

sql server how to find the max of a count row

I have written the following query:

DECLARE @countryCode TINYINT
SET @countryCode = 1;

SELECT 
    DivingSite.SiteCode, 
    DATEPART(Month, divingDate) AS 'month number' , 
    COUNT (divingNo) AS 'number of dives in month'
FROM 
    DivingSite
INNER JOIN
    tblDiving ON DivingSite.SiteCode = tblDiving.SiteCode
WHERE 
    DivingSite.countryCode = @countryCode 
    AND divingDate > DATEADD(year, -1, GETDATE())
GROUP BY
    DivingSite.SiteCode, DATEPART(Month, divingDate)

The result of this query is:

siteCode | month number | number of dives in month
--------------------------------------------------
   107          1                     1
   108          7                     2
   107          8                     2

The thing is - for every siteCode I want to display only the month with the biggest number of dives. So I would like to have the result:

siteCode | month number | number of dives in month
--------------------------------------------------
   108          7                     2
   107          8                     2

How do I do that?

Upvotes: 1

Views: 51

Answers (2)

marc_s
marc_s

Reputation: 754458

Try this - use a CTE (Common Table Expression) with a ROW_NUMBER function that "partitions" your data by the siteCode and numbers each row for a single siteCode starting at one - with the one with the highest number of dives having RowNum = 1 and any others having higher row numbers.

By selecting only those rows with RowNum = 1 you get only those entries for each siteCode with the highest number of dives.

DECLARE @countryCode TINYINT
SET @countryCode = 1;

;WITH RawData AS
(
    SELECT 
        ds.SiteCode, 
        MonthNumber = DATEPART(Month, divingDate), 
        NumberOfDives = COUNT (divingNo),
        RowNum = ROW_NUMBER() OVER (PARTITION BY ds.SiteCode ORDER BY COUNT(divingNo) DESC)
    FROM 
        dbo.DivingSite ds
    INNER JOIN
        dbo.tblDiving d ON ds.SiteCode = d.SiteCode
    WHERE 
        ds.countryCode = @countryCode 
        AND divingDate > DATEADD(year, -1, GETDATE())
    GROUP BY
        ds.SiteCode, DATEPART(Month, divingDate)
)
SELECT
   SiteCode,
   MonthNumber,
   NumberOfDives
FROM
   RawData
WHERE
   RowNum = 1

Upvotes: 1

mrtig
mrtig

Reputation: 2267

You could create a query on top of the one you already have, like so:

    select 
        DivingSite.SiteCode, 
        DATEPART(Month, divingDate) as 'month number' , 
        count (divingNo) as 'number of dives in month'
    into #dives
    from DivingSite
        inner join tblDiving on DivingSite.SiteCode = tblDiving.SiteCode
    where 
        DivingSite.countryCode = @countryCode 
        and divingDate > DATEADD(year,-1,GETDATE())
    group by 
        DivingSite.SiteCode, 
        DATEPART(Month, divingDate)


    select d.* from #dives d join 
    (
         SELECT 
             SiteCode, 
             MAX([number of dives in month]) MaxDives
         FROM #dives
         GROUP BY SiteCode
    ) max_dives on max_dives.SiteCode = d.SiteCode 
         and d.[number of dives in month] = max_dives.MaxDives

Upvotes: 0

Related Questions