title: Get max count of each group
body: <p>I have a table in MS access with 2 fields (payroll and absenceReason). There is one record per instance of absence.</p>
  
  <p>I am trying to write an SQL query to return the most frequent absence reason for each person. I'm sure there's a simple way to do this but I can't quite seem to get it. I've tried writing the below query:</p>
  
  <pre><code>SELECT payroll, reason, max(count)
  FROM
  (
  SELECT payroll, reason, count(reason) as count
  FROM table1
  GROUP BY payroll, reason
  ) 
  GROUP BY payroll, reason
  </code></pre>
  
  <p>but this returns every reason for each person. If I remove the reason from the GROUP BY clause then it doesn't work as I haven't aggregated reason in the select part. Can anyone point out where I'm going wrong?</p>
  
  <p>Thanks</p>