title: Select most frequently occurring records using two or more grouping columns
body: <p>I have a table which has no primary key and I can't add one - the relevant columns from it are:</p>
  
  <pre><code>Department   | Category  | 
  -------------+-----------+
  0001         | A         |
  0002         | D         |
  0003         | A         | 
  0003         | A         |
  0003         | C         |
  0004         | B         |
  </code></pre>
  
  <p>I want to retrieve a single row for each <code>Department</code>, which gives me the department code and the <code>Category</code> which appears most frequently in the table, i.e.</p>
  
  <pre><code>Department   | Category  | 
  -------------+-----------+
  0001         | A         |
  0002         | D         |
  0003         | A         | 
  0004         | B         |
  </code></pre>
  
  <p>What is the best way to achieve this?  My current attempt involves a <code>Count(Category)</code> in a subquery from which the <code>Max(CountofCategory)</code> is then taken, but including the <code>Category</code> field at this stage means too many rows at returned (since <code>GROUP BY</code> is applied at <code>Category</code> level as well as <code>Department</code>).  In the case of a tie, I'd just select the min/max of the category arbitrarily.  Ideally this should be database-agnostic, but is likely to run on either Oracle or MySQL.</p>