SQL - selecting rows with highest group by count

Beamson
Beamson used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
can you please show few rows you are getting?
Database Expert
Awarded 2016
Top Expert 2016
Commented:
You can do like below -


;WITH CTE AS
(
	/*Your code here*/
	/* alias COUNT(*) cnt */
)
SELECT * FROM 
(
	SELECT * , ROW_NUMBER() OVER (PARTITION BY Part ORDER BY cnt DESC) rnk
	FROM CTE
)u WHERE rnk = 1	

Open in new window

Author

Commented:
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

Author

Commented:
Yes, that works - thanks!
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial