Select max value from table by category-CodePudding

Bought X times	Name	Manufacturer
19	Олівець Faber-Castell, Castell 9000 F	FaberCastell
14	Набір акрилових глянцевих фарб C. Kreul El Greco 18 кольорів	Kreul
10	Набір акварельних фарб "Класика", ROSA Gallery, 12кол.	ROSA GROUP
7	Професійний акварельний пензлик Winsor & Newton - One Stroke	Winsor & Newton
6	Ластик електричний Derwent	Derwent
6	Гумка Koh-i-Noor 300/80 Слон	Koh-I-Noor
5	Набір маркерів Copic Sketch Set Skin Tones, 6 шт	Copic
4	Склейка для акварелі Derwent Inktense 300г 20л	Derwent
4	Пастель Van Gogh Royal Talens 60 кольорів	Royal Talens
4	Блокнот American Crafts Journal Studio	American Crafts
3	Кольорові олівці Prismacolor Premier 72 кольору в металевому пеналі	Prismacolor

I have created a query which generates results like those above. And, as you can see, there are two rows which contain Derwent as Manufacturer. I would like to leave only rows from each Manufacturer where "Bought X times" (a column, generated by Count()) is max. How can I do that? As a result, I would like to get

Bought X times	Name	Manufacturer
19	Олівець Faber-Castell, Castell 9000 F	FaberCastell
14	Набір акрилових глянцевих фарб C. Kreul El Greco 18 кольорів	Kreul
10	Набір акварельних фарб "Класика", ROSA Gallery, 12кол.	ROSA GROUP
7	Професійний акварельний пензлик Winsor & Newton - One Stroke	Winsor & Newton
6	Ластик електричний Derwent	Derwent
6	Гумка Koh-i-Noor 300/80 Слон	Koh-I-Noor
5	Набір маркерів Copic Sketch Set Skin Tones, 6 шт	Copic
4	Пастель Van Gogh Royal Talens 60 кольорів	Royal Talens
4	Блокнот American Crafts Journal Studio	American Crafts
3	Кольорові олівці Prismacolor Premier 72 кольору в металевому пеналі	Prismacolor

Query:

SELECT Count(Goods.Contract) AS [Bought X times], Goods.Goods_name AS [Name], Producer.Company AS [Manufacturer]
FROM Producer INNER JOIN (Goods INNER JOIN [Goods-Check] ON Goods.Goods_code= [Goods-Check].Goods_code) ON Producer.Contract= Producer.Contract
GROUP BY Goods.Goods_name, Producer.Company
ORDER BY Count(Goods.Contract) DESC;

CodePudding user response：

An old fashioned solution is to filter rows where another row with greater value does not exists:

SELECT *
FROM Query1 AS t
WHERE NOT EXISTS (
    SELECT 1
    FROM Query1 AS x
    WHERE x.Manufacturer = t.Manufacturer
    AND   x.[Bought X times] > t.[Bought X times]
)

This is standard SQL, not sure if it works in MS Access.