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

SQL Grouped on column A, column B records with "higest count"

I would like to select items from a table:
- Grouped on column_A
- With the value of column_B that has the highest count of records

column_A, column_B
car, red
car, red
car, green
motorbike, yellow
motorbike, blue
motorbike, orange
motorbike, orange

I would want the results
column_A, column_B
car, red
motorbike, orange

Where there are equal highest counts, I'm happy to take either value for column_B
1 Solution
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Let me know if this floats your boat.  Guessing there's a more elegant way to pull this off but this meets your requirements.
CREATE TABLE #foo (column_A VARCHAR(10), column_B VARCHAR(10))

INSERT INTO #foo (column_A, column_B) 
	('car', 'red'), 
	('car', 'red'), 
	('car', 'green'), 
	('motorbike', 'yellow'), 
	('motorbike', 'blue'), 
	('motorbike', 'orange'), 
	('motorbike', 'orange')

;WITH cte AS (
SELECT Column_A, Column_B, COUNT(Column_B) AS the_count
FROM #foo 
GROUP BY Column_A, Column_B), 
cte_grouped AS (
SELECT Column_A, Column_B, 
	RANK() OVER (PARTITION BY Column_A ORDER BY the_count DESC) AS rank_order
FROM cte) 
SELECT Column_A, Column_B FROM cte_grouped WHERE rank_order = 1

Open in new window

BeamsonAuthor Commented:
That does it for me!

Thanks Jim
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: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

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