読者です 読者をやめる 読者になる 読者になる

漆黒な技術メモ

管理人が必要に応じて自分のメモを好き勝手に投下するたまり場的ブログ

SQLで「グループごとにn件出力する」というSELECT文を書く

皆様あけましておめでとうございます。本年もどうぞよろしくお願いいたします。
さて、今日はSQLを久しぶりにいじっていて詰まったことを書きたいと思います。

下のようなテーブルがあって、「SSIDごとにステータスがONの最新の結果をn件出力する」ということを実行しなければならない場面に遭遇しました。 例を出すと、下のようなテーブルから「ステータスがONの最新の結果を1件出力したい」とします。

id time ssid status
1 12:00 ssid1 ON
2 12:00 ssid2 ON
3 12:00 ssid3 ON
4 12:10 ssid1 ON
5 12:10 ssid2 ON
6 12:10 ssid3 ON
7 12:20 ssid1 ON
8 12:20 ssid2 OFF
9 12:20 ssid3 ON

すると、上のテーブルから下のような結果が出てくるのが理想になります。

id time ssid status
7 12:20 ssid1 ON
5 12:10 ssid2 ON
9 12:20 ssid3 ON

しかしこのような抽出は、group by句、where句などの単純なものではできません…
さて、どうするかと思って検索するとこのような記事に引っかかりました。

blogs.wankuma.com

今回はこの記事を全力で参考にして解決しました。

解法:IN句を用いた副問い合わせ

これを見た瞬間「ああ、副問い合わせとかそんなものあったなあ」となりました…(←ダメな奴)
最初に示した結果を抽出するには以下のようなSQLを書きます。

select * from table as t1
where id in 
(select id from table as t2 where t1.ssid=t2.ssid and status='ON' order by id desc limit 1)
order by ssid, id desc;

主問い合わせ文のwhere句にはid in の形で指定し、副問い合わせ文のwhere句の中でグループにしたいカラムを結合します(この場合は t1.ssid=t2.ssid )。
また、「ステータスがONの」などの他の条件や「N研抽出する」などのLIMIT句も副問い合わせ文の中に記述します。
あとは適宜並び替えなどをすれば完了です。

今日はここまで!