title: access db, select group by having max problem
body: <p>in <strong>Access DB</strong>...
  I need to extract the itemcode / desc combination for each itemcode in the following table where the desc has been used most frequently.</p>
  
  <p><strong>most frequently could possibly mean that there was only one version (see added record for pear)</strong></p>
  
  <p>in the case of <strong>itemcode 777</strong>, I'll have to decide later which description version to use.  if there are multiple records, each containing a single version of a description, that will definately create an additional problem.</p>
  
  <p>the original question should probably also include returning the first row for itemcodes like 777 where all the existing records for an itemcode contain a single, unique description (so that the count would always be 1).  the first row may not always be the correct version - but I won't be able to automate that phase anyway.</p>
  
  <pre><code>---------------------
  itemcode  | desc
  ---------------------
  123       | apple
  123       | apple
  123       | apple
  123       | apple 2
  123       | apple-2
  001       | orange
  001       | orange
  001       | ORANGE 1
  001       | orange-1
  666       | pear
  777       | bananananana
  777       | banana
  </code></pre>
  
  <p>so - I'm looking to end up with the following:</p>
  
  <pre><code>---------------------
  itemcode  | desc
  ---------------------
  123       | apple
  001       | orange
  666       | pear
  777       | bananananana
  </code></pre>
  
  <p>I think I'm close, but the following only gets the description in the database which appears most frequently and only returns one row.  </p>
  
  <pre><code>SELECT itemcode, desc, count(desc)
  from table
  group by itemcode, desc
  having count(desc) = 
  (
   select max(ct) from 
   (
    select itemcode, desc, count(desc) as ct
    from table
    group by itemcode, desc
    )
  );
  </code></pre>
  
  <p>returns:</p>
  
  <pre><code>---------------------
  itemcode  | desc
  ---------------------
  123       | apple
  </code></pre>