CODEWITHSUNDEEP
sqldatabasems-access
OldBuildingAndLoan
OldBuildingAndLoan

Reputation: 3022

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

Upvotes: 2

Views: 3203

Answers (4)

JP Alioto
JP Alioto

Reputation: 45117

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!

Upvotes: -1

Daniel Brückner
Daniel Brückner

Reputation: 59645

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.

Upvotes: 0

Tomalak
Tomalak

Reputation: 338208

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.

Upvotes: 3

MarlonRibunal
MarlonRibunal

Reputation: 4097

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

Upvotes: 0

Related Questions