title: How can I count the number of items in a grouped query and display Top (x)?
body: <p>I'm trying to display the highest count of errors from the results of a group by query</p>
  
  <p>I think I've got the initial group correct but can't figure out the sub query to count the number of errors by module and filter out the highest counts.</p>
  
  <pre><code>SELECT Code AS Module
     , ErrCode As ErrorCode
     , Count(ErrCode) AS ErrorCount
     , CAST([Msg-EN] as varchar(max)) as ErrorMsg
  FROM ViewAllAlarm
  WHERE RiseTime &gt;= DateAdd(Hour, -12, GetDate())
  GROUP BY Code, ErrCode,  CAST([Msg-EN] as varchar(max))
  Order by Module ASC
  </code></pre>
  
  <p>Result:</p>
  
  <pre>
  Module | ErrorCode | ErrorCount | ErrorMsg
  -------+-----------+------------+---------
  F108   | 194       |        127 | Error2
  F108   | 358       |        209 | Error1
  F109   | 129       |         11 | Error3
  F110   | 129       |        200 | Error3
  F110   | 358       |        300 | Error1
  F111   | 358       |          2 | Error1
  F111   | 129       |          5 | Error3
  F112   | 129       |         12 | Error3
  F113   | 358       |        200 | Error1
  F114   | 194       |        300 | Error2
  </pre>
  
  <p>I now need to total the amount of errors (ErrorCount) by module and display the modules with the highest counts (top 5).</p>
  
  <p>This gives 336 errors for F108, 11 for F109, 500 for F110, etc. I want to show the five modules with the highest error counts, which are F110(500), F108(336), F114(300), F113(200), F112(12). Modules F129(11) and F111(7) shall not be shown.</p>
  
  <pre>
  Module | ErrorCode | ErrorCount | ErrorMsg
  -------+-----------+------------+---------
  F108   | 194       |        127 | Error2
  F108   | 358       |        209 | Error1
  F110   | 129       |        200 | Error3
  F110   | 358       |        300 | Error1
  F112   | 129       |         12 | Error3
  F113   | 358       |        200 | Error1
  F114   | 194       |        300 | Error2
  </pre>
  
  <p>This is on MS 2016 SQL server.</p>