CODEWITHSUNDEEP
mysql
SEU
SEU

Reputation: 1390

Mysql: Obtain the largest number of occurences of a city in a table

I have a table which has some data and corresponding city, state. I would like to see the largest number of occurences of a city in a particular state. Say if there are 100 occurences of Denver,CO and 10 occurences of Boulder,CO, I would like to see only Denver, since that's the largest number. I tried the following, but it lists all other cities too:

select state, city, count(city) 
from tab12 
group by city 
order by state, count(city);

Any help is appreciated. Thanks.

Upvotes: 0

Views: 92

Answers (3)

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44343

SELECT A.* FROM
(
    SELECT city,state,COUNT(1) citycount
    FROM tab12 GROUP BY state,city
) A INNER JOIN
(
    SELECT state,MAX(citycount) citycount FROM
    (SELECT state,city,COUNT(1) citycount
    FROM tab12 GROUP BY state,city) C
) B
USING (state,citycount)
ORDER BY A.state,A.city;

This query should work for states that have multiple cities with the same count.

Upvotes: 1

Razvan
Razvan

Reputation: 10093

SELECT state, city, max(cnt)
FROM
(select state, city, count(city) as cnt
 from tab12 group by city order by state, count(city))
order by state

If you want to cover also cases in which you might have cities with the same frequency: 

SELECT state, city, count(*) cnt 
FROM cities GROUP BY state,city  
HAVING cnt = (SELECT max(cnt) 
              FROM 
              (SELECT count(*) cnt FROM cities group by state,city) as tbl) 
ORDER BY state;

Upvotes: 1

Michael Rusch
Michael Rusch

Reputation: 2617

Untested, off the top of my head:

select state as thestate, city as thecity, count(*) as thecount
from tab12
group by state, city
having thecount = (
  select max(subcnt)
  from(
    select count(*) as subcnt
    from tab12 sub
    where sub.state = thestate
    group by sub.city
  )
)

Note that in the case of a tie, this will return every city that is tied fro the highest count.

Upvotes: 0

Related Questions