Question: 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?

Answer: 
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

```
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

```