How to get frequent element of the column in SQL?

user_18571370 0
Viewed 28

I would like to get frequent element of the column in SQL.

For instance,

Assume we have the below column,
Jack
Jack
Jack
Jeremy
Katie
Katie
John

I would like to get Jack.

Furthermore in below case,
Jack
Jack
Jack
Jeremy
Katie
Katie
Katie
John

I would like to get Jack and Katie.

What I have tried?

GROUP BY COLUMN ORDER BY COUNT(*) DESC LIMIT 1;

It works in first case, but since we are limiting it by 1, second case fails and only returns Jack instead of Jack and Katie.

TL;DR I would like to get most frequent elements even if their frequency are the same.

Thanks in advance.

mysql sql
2 Answers
select <column>, count(*) as c 
from <table> 
group by <column> 
having c = (
  select count(*) 
  from <table> 
  group by <column> 
  order by count(*) desc 
  limit 1
)
user_20791890

You can try to use HAVING subquery

SELECT name,COUNT(*) cnt
FROM T
GROUP BY name
HAVING COUNT(*) = (
  SELECT COUNT(*) cnt
  FROM T
  GROUP BY name
  ORDER BY COUNT(*) DESC
  LIMIT 1
)

if your MySQL version support the window function, you can try this.

SELECT *
FROM (
  SELECT *,dense_rank() over(order by cnt desc) rn 
  FROM (
   SELECT *,COUNT(*) OVER(PARTITION BY name) cnt
   FROM T
  ) t1
) t1
WHERE rn = 1

sqlfiddle

user_51760710
Related