最大値の取得に条件をつける
SQLで最大値を取得するときに、条件を指定したい場合もありますよね。
この記事では、最大値の取得に条件をつける方法について解説します。
ここで紹介するのは、次の2つです。
- WHERE句
- GROUP BY HAVING句
1つずつ見ていきましょう。
（１）WHERE句
まずは、最大値を取得する際に WHERE句 を使って、条件をつける方法を見ていきましょう。
次のようなテーブルを用意しました。
| id | name | score | dt |
| 1 | Watanabe | 147 | 2020-03-01 |
| 2 | Noda | 176 | 2020-04-01 |
| 3 | Fukuda | 185 | 2020-05-01 |
| 4 | Hashimoto | 166 | 2020-06-01 |
| 5 | Seto | 159 | 2020-07-01 |
①指定した条件の中での最大値を取得する
まずは、指定した条件の中での最大値を取得する方法について見ていきましょう。
ここでは、指定した日付(dt)以降のデータの中でスコアの最大値を取得してみます。
SQL文は次の通りです。
SELECT MAX(score) FROM sample_table WHERE dt >= '2020-06-01';
取得するカラム部分にMAX関数を使ってカラムを指定します。
そして、WHERE句で指定する日付の条件を「2020-06-01」以上と設定しました。
上記のSQL文を実行すると、次のデータを取得できます。
mysql> SELECT MAX(score) FROM sample_table WHERE dt >= '2020-06-01'; +------------+ | MAX(score) | +------------+ | 166 | +------------+ 1 row in set (0.00 sec)
サンプルのデータだと「2020-06-01」と「2020-07-01」の2択になりますが、最大値が取得できていますね。
②テーブルから日付が最大（最新）のデータを取得する
次に、テーブル内にあるデータから日付が最大（最新）のデータを取得する方法です。
サンプルのテーブルだと「2020-07-01」になりますね。
SQL文は次の通りです。
SELECT * FROM sample_table WHERE dt = (SELECT MAX(dt) FROM sample_table);
ここでは、最新の日付のデータから全てのカラムを取得しています。
WHERE句でサブクエリ（副問い合わせ）を使って、最新の日付を条件に設定しています。
シンプルではありますが、これで最新の日付のデータを取得可能です。
上記SQL文を実行すると、次のようにデータを取得できます。
mysql> SELECT * FROM sample_table WHERE dt = (SELECT MAX(dt) FROM sample_table); +------+------+-------+------------+ | id | name | score | dt | +------+------+-------+------------+ | 5 | Seto | 159 | 2020-07-01 | +------+------+-------+------------+ 1 row in set (0.01 sec)
なお、サブクエリの使い方については次の記事で詳しく解説しています。
【関連記事】
▶︎SQLの副問い合わせ（サブクエリ）とは？概要・種類・使い方を解説
（２）GROUP BY HAVING句
次に、GROUP BY HAVING句 を使って、最大値の取得に条件をつける方法を見ていきましょう。
先ほど用意したテーブルに classカラム を追加しました。
| id | class | name | score | dt |
| 1 | 1 | Watanabe | 147 | 2020-03-01 |
| 2 | 2 | Noda | 176 | 2020-04-01 |
| 3 | 1 | Fukuda | 185 | 2020-05-01 |
| 4 | 1 | Hashimoto | 166 | 2020-06-01 |
| 5 | 2 | Seto | 159 | 2020-07-01 |
では、このテーブルを使って、classごとの日付の最大値(最新日)を取得してみましょう。
次のように記述します。
SELECT class, MAX(dt) FROM sample_table GROUP BY class;
上記SQL文を実行すると、次のように結果を取得できます。
mysql> SELECT class, MAX(dt) FROM sample_table GROUP BY class; +-------+------------+ | class | MAX(dt) | +-------+------------+ | 1 | 2020-06-01 | | 2 | 2020-07-01 | +-------+------------+ 2 rows in set (0.00 sec)
ちゃんとそれぞれの classカラム ごとに最新の日付が取得できていますね。
例えば、このテーブルで「6月以降のデータのあるものは取得したくない」など条件をつけたい場合はHAVING も合わせて使いましょう。
次のように記述できます。
SELECT class, MAX(dt) FROM sample_table GROUP BY class HAVING MAX(dt) < '2020-07-01';
上記SQL文を実行すると、次のように結果を取得できます。
mysql> SELECT class, MAX(dt) FROM sample_table GROUP BY class HAVING MAX(dt) < '2020-07-01'; +-------+------------+ | class | MAX(dt) | +-------+------------+ | 1 | 2020-06-01 | +-------+------------+ 1 row in set (0.01 sec)
「2020-07-01」は除外されているのがわかりますね。
最大値の条件を複数設定する
ここまで、WHERE句とGROUP BY HAVING句の使い方を紹介しました。
では次に、最大値の条件を複数設定する方法を見ていきましょう。
実は、WHERE句とGROUP BY HAVING句は組み合わせて指定することも可能です。
例えば、WHERE句 でスコアの最大値が160未満のデータを classカラム ごとに取得するには、次のように記述します。
SELECT class, MAX(score) FROM sample_table WHERE score < 160 GROUP BY class;
上記SQL文を実行すると、次のように結果を取得できます。
mysql> SELECT class, MAX(score) FROM sample_table WHERE score < 160 GROUP BY class; +-------+------------+ | class | MAX(score) | +-------+------------+ | 1 | 147 | | 2 | 159 | +-------+------------+ 2 rows in set (0.01 sec)
期待した通りのデータを取得できましたね。
では、先ほど同様に、HAVING句も使って条件を絞ります。
スコアが160未満のデータを classカラム ごとに取得し、150より大きい最大値を取得してみましょう。
次のように記述できます。
SELECT class, MAX(score) FROM sample_table WHERE score < 160 GROUP BY class HAVING MAX(score) >150;
上記SQL文を実行すると、次のように結果を取得できます。
mysql> SELECT class, MAX(score) FROM sample_table WHERE score < 160 GROUP BY class HAVING MAX(score) >150; +-------+------------+ | class | MAX(score) | +-------+------------+ | 2 | 159 | +-------+------------+ 1 row in set (0.00 sec)
ちゃんと指定したデータが取得できました。
また、WHERE句 を AND でつなげて条件を複数指定することも可能です。
例えば、次のように「2020-01-01」から「2020-04-30」の間でスコアの最大値も取得できます。
SELECT MAX(score) FROM sample_table WHERE dt >= '2020-01-01' AND dt <= '2020-04-30';
実行結果は次の通りです。
mysql> SELECT MAX(score) FROM sample_table WHERE dt >= '2020-01-01' AND dt <= '2020-04-30'; +------------+ | MAX(score) | +------------+ | 176 | +------------+ 1 row in set (0.01 sec)
3つ目に条件を追加して、取得するデータをより絞ることもできます。
SELECT MAX(score) FROM sample_table WHERE dt >= '2020-01-01' AND dt <= '2020-04-30' AND score <= 160;
実行結果は次の通りです。
mysql> SELECT MAX(score) FROM sample_table WHERE dt >= '2020-01-01' AND dt <= '2020-04-30' AND score <= 160; +------------+ | MAX(score) | +------------+ | 147 | +------------+ 1 row in set (0.01 sec)
