CODEWITHSUNDEEP
mysqlselect
eveningnectarine
eveningnectarine

Reputation: 33

MySQL - SELECT Most occuring value (with several same occurrences)

I have been looking for a way to do a MySQL select to get all the most occuring values. All the solutions i have found where with using LIMIT 1, but this doesn't help if there are more than one value that occur the same amount of times. E.g:

customer

ID FirstName
1 Bob
2 Tom
3 Bob
4 Robert
5 Tom
6 Timothy

The Select for most occuring FirstNames should result in:

FirstName
Bob
Tom

Since both occur twice.

I have tried the following:

SELECT FirstName FROM (
  SELECT FirstName, COUNT(FirstName) as counter FROM customer
  GROUP BY FirstName
  HAVING counter = MAX(counter)
) s

But this doesn't seem to work, i would really appreciate a nudge in the right direction.

Upvotes: 1

Views: 24

Answers (1)

Amit Verma
Amit Verma

Reputation: 2488

There can be different ways for doing this

you can try

1st

SELECT FIRSTNAME 
FROM customer
GROUP BY FirstName Having count(FirstName) = (
   SELECT COUNT(FirstName) FROM customer GROUP BY FirstName ORDER BY 1 
   DESC LIMIT 1);

2nd

with cte as
(
  SELECT COUNT(FirstName) MaxCounter
  FROM customer 
  Group By FirstName ORDER BY  COUNT(FirstName) DESC LIMIT 1
)

SELECT c.FirstName
From customer c
Group BY FirstName
HAVING COUNT(FirstName) = (SELECT MaxCounter FROM cte)

Upvotes: 1

Related Questions