All geek questions in one place

Select multiple max rows

Suppose I have a MySQL table named Domain . How to find the most popular email domain from this table. Directly I want to find all rows with the maximum number.

 Domain gmail.com gmail.com gmail.com yahoo.com yahoo.com yahoo.com rediffmail.com rediffmail.com mail.com

My desired result:

 Domain No_of_users gmail.com 3 yahoo.com 3
+4
source share
2 answers
 select x.Domain, x.No_of_users from (select d.Domain, count(d.Domain) as No_of_users from Domain d group by d.Domain) x where x.No_of_users = (select max(x2.No_of_users) from (select d2.Domain, count(d2.Domain) as No_of_users from Domain d2 group by d2.Domain) x2)
+2
source 
 SELECT Domain, count(id) FROM Domain GROUP BY Domain.Domain HAVING count(id) = (SELECT count(id) FROM Domain D GROUP BY D.Domain ORDER BY count(id) DESC LIMIT 1)
+2
source

Source: https://habr.com/ru/post/1435658/

More articles:


All Articles