I looked at this - it's an interesting question (+1).
I also added sample data - it's always difficult to cover edge-cases, but it's worth looking at (data and entire analysis on fiddle here).
So, we have:
SELECT * FROM inventory;
Result:
PRODUCTNUMBER DESCRIPTION
ABC Product ABC (with discount)
ABC Product ABC
ABC Product ABC
DEF Product DEF
XYZ Product XYZ -- <<< extra data from here.
XYZ Product XYZ
XYZ Product XYZ
XYZ Product XYZ (with discount 1)
XYZ Product XYZ (with discount 2)
XYZ Product XYZ (with discount 3)
RST Product RST with discount -- the interesting two records!
RST Product RST without discount
I formulated the following SQL (I've shown the early steps that led me to my answer - partly for your understanding, partly for my own! :-) ):
SELECT
DISTINCT
t2.ProductNumber AS "Product Number",
t1.Description AS "Product Desc."
FROM
(
SELECT i1.Description, COUNT(i1.Description) AS cnt,
ROW_NUMBER() OVER (PARTITION BY i1.ProductNumber ORDER BY COUNT(i1.Description) DESC) AS rn
FROM inventory i1
GROUP BY i1.Description, i1.ProductNumber
) t1
INNER JOIN
(
SELECT i2.ProductNumber, i2.Description
FROM inventory i2
) t2
ON t1.Description = t2.Description
WHERE t1.rn = 1
ORDER BY 1;
Result:
Product Number Product Desc.
ABC Product ABC
DEF Product DEF
RST Product RST without discount
XYZ Product XYZ
Now, notice that my SQL has chosen the RST, Product RST without discount record where there was a choice between both RST (with and without discount) records.
Now, you will see from the fiddle that Lennart's query returns the other RST record.
You might want to investigate that and ensure that you retrieve the records that you require under all circumstances.
I've also looked at the plans for the queries. I found out how to do this using the fiddle from an article by Franck Pachot here and the associated Oracle fiddle here.
Now, I'm far from an expert on Oracle plans, but given their similarity, I can't tell whether Lennart's query or my own would be the more efficient given an large dataset. I would urge you to test with realistic datasets.
The only "concrete" benefit that accrues from my query is that it can be used on antique versions of servers that don't support ROW_NUMBER() - but even MySQL has those now, so it's unlikely to a significant gain! :-)
p.s. you will notice that I have modified your query slightly to use ANSI joins as follows:
select /*+ gather_plan_statistics */ distinct cnt1.ProductNumber
from
(
select COUNT(*) as total, ProductNumber
from Inventory
group by ProductNumber
) cnt1
JOIN
(
select MAX(total) as maxtotal, ProductNumber
from
(
select COUNT(*) as total, ProductNumber
from Inventory
group by ProductNumber
) t
group by ProductNumber
) cnt2
ON cnt1.total = cnt2.maxtotal;
I hope you find this helpful and/or more readable? I think that yours performs a CROSS JOIN and then filters which can be problematic with large datasets?
