今更だけどSQLでcountした中からmax値を取得する

ちょっとSQL書く機会減ってきて、久しぶりに触れたときに忘れかけていたことを記事として残しておくよ！普段からSQL書く人は当たり前のことだから鼻でもほじりながら読んでね！

本記事の対象者

• 私
• SQL初心者
• GroupBy知らない人
• 改めてSQLのちょっと複雑な処理復習したい人

前提条件

本記事は以下のテーブルを操作することを前提としております。

select * from city

id|name|coutryCode|distinct|population
---|---|---|---|---|---
1|Kabl|AFG|Kabol|1780000
2|Qandahar|AFG|Quandahar|237500
3|Herat|AFG|Herat|186800
･･･|･･･|･･･|･･･|･･･

早速操作していくよ！

タイトル通り、前提条件で記載したテーブルから、SQLでcountした中からmaxを取得するよ！
具体的には、「一番多いcountryCodeは何か」を調査するよ！

1. countryCodeでグループわけして、各countryCodeの出現回数を取得するよ！

select countryCode, count(*) from city group by countryCode;

countryCode	count(*)
ABW	1
AFG	4
AGO	5
AIA	2
ALB	1
AND	1
ANT	1
･･･	･･･

2. 1で分けたグループの中から、最大値を取得するよ！

select max(cnt) from 
(select countryCode, count(*) cnt from city group by countryCode) num;

max(cnt)
363

サブクエリはほぼ1と同じですが、少し修正しています。理由として、count(*)に名前付けをしないとmax関数がうまく動作しません。cntと名前をつけるときには、as cntでもOK。

3. 1でグループ分けしたデータから、2で抽出した値と同じcountryCodeを取得する。

select temp.countryCode from 
(select countryCode, count(*) cnt2 from city group by countryCode) temp
    where temp.cnt2 = (
        select max(cnt) from (
            select countryCode, count(*) cnt from city group by countryCode
            ) num
        );

countryCode
CHN

最初のサブクエリは、1で出力した内容。
where temp.cnt2 = 以降のサブクエリは2で取得した値です。

最後に今回のおさらい

countした結果の最大値を取得するのは意外と難しい。。。
ただし、今回のようにサブクエリ１つずつ順を追っていけば理解も深まるかと思います。

気が向いたら、同様のクエリをhaving句やall句などを用いて書き換えてみようと思います。
ご精読ありがとうございました。