• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 41
  • Last Modified:

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?
0
Beamson
Asked:
Beamson
  • 3
  • 2
1 Solution
 
Pawan KumarDatabase ExpertCommented:
can you please show few rows you are getting?
0
 
Pawan KumarDatabase ExpertCommented:
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

0
 
BeamsonAuthor 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

0
 
BeamsonAuthor Commented:
Yes, that works - thanks!
1
 
Pawan KumarDatabase ExpertCommented:
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

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now