SQL - selecting rows with highest group by count

I have a large table with 2 significant columns of data
Part
ApplianceType

In order to set a generic description for each Part, I need to find the most prominent value of ApplianceType.

So in a GROUP BY Part, ApplianceType, I'd want only those rows returned where the COUNT(*) was highest for Part.

What is the best way of doing this?

SQL

Last Comment

Pawan Kumar

8/22/2022 - Mon

Pawan Kumar

11/24/2017

can you please show few rows you are getting?

ASKER CERTIFIED SOLUTION

Pawan Kumar

11/24/2017

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

Beamson

11/24/2017

ASKER

For raw data:

Part	ApplianceType
A001	Plasma Television
A001	Plasma Television
A001	LCD Television
A001	Plasma Television
A001	Plasma Television
A001	Plasma Television
A002	DVD Player
A002	HDD/DVD Player
A002	HDD/DVD Player

Open in new window

I'd want to select those rows with the max count of group by:

Part	ApplianceType
A001	Plasma Television
A002	HDD/DVD Player

Open in new window

Beamson

11/24/2017

ASKER

Yes, that works - thanks!

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat

William Peck

Pawan Kumar

11/24/2017

Please find full tested solution for you

Data generation and data insertion

CREATE TABLE MAxGROUP
(
	 Part	VARCHAR(5)
	,ApplianceType VARCHAR(50)
)
GO

INSERT INTO MAxGROUP VALUES
('A001','Plasma Television'),
('A001','Plasma Television'),
('A001','LCD Television'),
('A001','Plasma Television'),
('A001','Plasma Television'),
('A001','Plasma Television'),
('A002','DVD Player'),
('A002','HDD/DVD Player'),
('A002','HDD/DVD Player')
GO

Open in new window

SOLUTION

SELECT Part,ApplianceType
FROM 
(
	SELECT * , ROW_NUMBER() OVER (PARTITION BY Part ORDER BY cnt DESC) rnk
	FROM 
	(
		SELECT Part,ApplianceType,COUNT(*) cnt
		FROM MAxGROUP
		GROUP BY Part,ApplianceType
	)r
)p WHERE rnk = 1

Open in new window

OUTPUT

/*------------------------
OUTPUT
------------------------*/
Part  ApplianceType
----- --------------------------------------------------
A001  Plasma Television
A002  HDD/DVD Player

(2 row(s) affected)

Open in new window