in Access DB... I have to extract the itemcode / desc combination for every itemcode within the following table in which the desc has been utilized most often.
most often might imply that there is just one version (see added record for pear)
within the situation of itemcode 777, I'm going to decide later which description version to make use of. if you will find multiple records, each that contains just one version of the description, which will certainly create one more problem.
the initial question should most likely likewise incorporate coming back the very first row for itemcodes like 777 where all of the existing records to have an itemcode have a single, unique description (to ensure that the count would continually be 1). the very first row might not continually be the right version - however i will not have the ability 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 am searching to finish track of the next:
--------------------- itemcode | desc --------------------- 123 | apple 001 | orange 666 | pear 777 | bananananana
I believe I am close, however the following only will get the description within the database which seems most often and just 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 ) );
--------------------- itemcode | desc --------------------- 123 | apple
This could sort out 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 (examined with Access 2003):
itemcode desc CountOfdesc 001 orange 2 123 apple 3 666 pear 1 777 blueberry 1
BTW you need to not really be calling a table "table" along with a column "desc". Individuals are reserved SQL key phrases, just prevent them to create your existence simpler.
Your query returns the MAX. Try to produce a rule that will match your needs.
That "which seems most oftenInch means what? appears>2? appears>3? appear>4?...
Fixed and examined. It really works as exspected - or better as designed, since it returns all rows when the greatest count to have an ItemCode seems several occasions.
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 )
Just simplifed the query a little.
Not able to ensure whether it works together with Access 2003. Tryed it, but Access keeps requesting
Wait, why can't you simply order through the count and go ahead and take top however many you would like? Will I get me wrong your question? For instance ...
SELECT TOP N itemcode, desc, count(desc) AS [Count] FROM table GROUP BY itemcode, desc ORDER BY [Count]
Okay, what 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 steer clear of the h8? :)
Ahhh Access! I quit!