Databases
StackOverflow2013
Postsdbo
POaccess db, select group by having max problem
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POaccess db, select group by having max problem
    text
    copied!<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>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload