title: sql server how to find the max of a count row
body: <p>I have written the following query:</p>
  
  <pre><code>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 &gt; DATEADD(year, -1, GETDATE())
  GROUP BY
      DivingSite.SiteCode, DATEPART(Month, divingDate)
  </code></pre>
  
  <p>The result of this query is:</p>
  
  <pre><code>siteCode | month number | number of dives in month
  --------------------------------------------------
     107          1                     1
     108          7                     2
     107          8                     2
  </code></pre>
  
  <p>The thing is - for every <code>siteCode</code> I want to display only the month with the biggest number of dives. So I would like to have the result:</p>
  
  <pre><code>siteCode | month number | number of dives in month
  --------------------------------------------------
     108          7                     2
     107          8                     2
  </code></pre>
  
  <p>How do I do that?</p>