access db, select group by having max problem

in Access DB... I need to extract the itemcode / desc combination for each itemcode in the following table where the desc has been used most frequently.

most frequently could possibly mean that there was only one version (see added record for pear)

in the case of itemcode 777, 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.

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.

---------------------
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

so - I'm looking to end up with the following:

---------------------
itemcode  | desc
---------------------
123       | apple
001       | orange
666       | pear
777       | bananananana

I think I'm close, but the following only gets the description in the database which appears most frequently and only returns one row.

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
  )
);

returns:

---------------------
itemcode  | desc
---------------------
123       | apple

From stackoverflow

• Your query returns the MAX. Find a way to create a rule that would satisfy your requirements.

  That "which appears most frequently" means what? appears>2? appears>3? appear>4?...

  42 : It could only appear 1 time. I would want that as well.

  MarlonRibunal : Look at JP's answer. Maybe that will be enough for what you're looking for. :-)

• Wait, why can't you just order by the count and take the top however many you want? Do I misunderstand your question? For example ...

  SELECT TOP N itemcode, desc, count(desc) AS [Count] FROM table
  GROUP BY itemcode, desc
  ORDER BY [Count]
  

  Okay, how about this ...

  ;WITH dt AS
  (
    SELECT 
    ROW_NUMBER() OVER 
    ( PARTITION BY itemcode ORDER BY COUNT([desc])DESC ) AS 'RowNumber',
    COUNT([desc]) AS [Count],
     itemcode,
     [desc] 
     FROM [table]
     GROUP BY itemcode, [desc]
  )
  SELECT * FROM dt WHERE dt.RowNumber = 1
  

  Will that stop the h8? :)

  Ahhh Access! I give up!

  Daniel Brückner : This will not work. 123 Apple 50 times 123 Apple2 60 times 001 Orange 20 times - select top 2 and you get 2 entries for 123 but none for 001.

  42 : sorry, that doesn't work for me.

  JP Alioto : Ah, I see know, okay.

  DJ : WTF? Posting T-SQL code will not go over well on a MS Access question..

• Fixed and tested. It works as exspected - or better as designed, because it returns all rows if the highest count for an ItemCode appears several times.

  SELECT ItemCode, ItemDescription, COUNT(ItemDescription) AS ItemCount
  FROM Items I1
  GROUP BY ItemCode, ItemDescription
  HAVING COUNT(ItemDescription) = 
     (SELECT MAX(ItemCount)
        FROM (
           SELECT COUNT(ItemDescription) AS ItemCount
           FROM Items I2
           WHERE I2.ItemCode = I1.ItemCode
           GROUP BY ItemDescription
        ) I3
     )
  

  UPDATE

  Just simplifed the query a bit.

  UPDATE

  Unable to verify if it works with Access 2003. Tryed it, but Access keeps asking for I1.ItemCode.

  42 : all the data is in one table. (not actually called table). this doesn't work for me.

  Tomalak : Unfortunately, this won't work with Jet SQL. The engine does not support nested correlated sub-queries, AFAIR.

  Daniel Brückner : Just tested it with SQL 2005 and I don't use Access so I cannot say if it works with access.

  Tomalak : I know... The MS Jet engine is just not capable of some of the nicer things you can do on SQL server.

  42 : yep. access has really crippled my brain when it comes to sql.

• This would work through a correlated sub-query:

  SELECT 
    t.itemcode, t.desc, Count(t.desc) AS CountOfdesc
  FROM 
    [table] AS t
  GROUP BY 
    t.itemcode, t.desc
  HAVING 
    Count(t.desc) IN (
      SELECT TOP 1 
        Count(i.desc)
      FROM 
        [table] AS i
      WHERE 
        i.itemcode = t.itemcode
      GROUP BY 
        i.itemcode, i.desc
      ORDER BY 
        Count(i.desc) DESC
    )
    AND t.desc = (
      SELECT TOP 1 
        i.desc
      FROM 
        [table] AS i
      WHERE 
        i.itemcode = t.itemcode
      GROUP BY 
        i.itemcode, i.desc
      ORDER BY 
        i.desc
    )
  ;
  

  Returns (tested with Access 2003):

  itemcode  desc          CountOfdesc
  001       orange        2
  123       apple         3
  666       pear          1
  777       banana        1
  

  BTW you should really not be calling a table "table" and a column "desc". Those are reserved SQL keywords, just avoid them to make your life easier.

  42 : "table" and "desc" were just stand-ins, not the real names.

  Tomalak : @42: Good. :)

  Tomalak : Yes, confirmed working in Access 2003. What version do you use?

  DJ : Confirmed working as well

  42 : Brilliant! Super! Awesome!