CODEWITHSUNDEEP
mysqlsql
AnchovyLegend
AnchovyLegend

Reputation: 12538

Grouping by column that has most matches

So I have a table with 500K records that contain business id's. Every business record can have anywhere from 0-500 different contacts tied to same business (via business_id).

I am trying to write a query that will find the business record that has the most contacts tied to it.

This is what I tried:

SELECT * FROM contacts GROUP BY business_id ORDER BY MAX(business_id)

I appreciate any advice on how to accomplish this, many thanks in advance!

Upvotes: 1

Views: 37

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

Get the count of business_id's and order by the count of business_id's

SELECT * ,count(business_id) as cbusiness_id 
FROM contacts 
GROUP BY business_id
ORDER BY cbusiness_id DESC

Upvotes: 1

Related Questions