【MySQL小ネタ】最小レコードを取得する時

初心者の時の、ついうっかりしがちな部分を思い出しながら書いている小ネタです。

一番小さいのは？

例えばユーザーごとに一番古いデータを取得する時。次のような簡単な注文データがあったとします。

CREATE TABLE order_data (
ユーザー名 varchar(11) DEFAULT NULL,
金額 int(10) DEFAULT NULL,
購入日 datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO order_data (ユーザー名, 金額, 購入日)
VALUES
('ユーザーA',1000,'2020-01-01 10:00:00'),
('ユーザーB',2000,'2020-01-02 00:00:00'),
('ユーザーB',2500,'2020-01-03 13:15:00'),
('ユーザーC',2000,'2020-01-04 14:00:00'),
('ユーザーC',1000,'2019-12-31 23:59:59'),
('ユーザーA',2000,'2020-01-04 19:30:00');

このようなデータを次のように実行するとどうでしょうか？

select ユーザー名,金額,min(購入日) from order_data where ユーザー名 = "ユーザーC"

ここではわかりやすいようにユーザーCのみを表示します。
ユーザーCの購入日は2019/12/31と2020/01/04なのでmin()で表示されるのは2019年のほうです。

その時の金額は1000なので1000が表示されると思って実行すると

2000という金額が表示されましたか？

同じ行にあるので、その行を抽出できるかと思ってやってしまいがちですが、実際はこのように違う数値が取れてしまいます。

select ユーザー名,金額,min(購入日) from order_data
group by ユーザー名

このようにほかのユーザーも一緒に出して、かつ他のデータは正しいように見えると見逃してしまいがちです。select句で書く集計関数は他の列には関係ない独立したものと覚えておきましょう。

となると、どのように書けば抽出できるでしょうか？

例えば一番小さい日付をwhereで指定してあげれば抽出できそうです。

select ユーザー名,金額,購入日 from order_data where 購入日 = "2019-12-31 23:59:59"

はい、抽出できました！でも、一人だけ出すのは簡単ですが他のユーザーも同様に抽出するのに一つ一つ条件を書いてたらキリがありません。

なのでwhereに一番小さい条件が来るようにするとよいと思います。

ちなみに私が昔書いたときこんな間違いしました（そもそも動きませんが）

select ユーザー名,金額,購入日 from order_data where min(購入日)#エラーが出て動きません

脱線しましたが例えばこのような書き方はどうでしょう。

select ユーザー名,金額,購入日 from order_data as m
where 購入日 =
(select min(購入日)as 初回 from order_data as s group by ユーザー名)

日付をwhereで指定できるようにサブクエリという方法で購入日の一番小さいものを拾います。
ただし、これは以下のようなエラーが出てしまいます。

Error Code: 1242. Subquery returns more than 1 row 0.000 sec

(select～)のサブクエリはこの場合、３行の結果を返します（各ユーザー分）。
「where 購入日 =」という書き方は1対1の関係になってなければいけないので、ここでは1:3（１対多の関係）となりエラーが出ます。

正しくは

select ユーザー名,金額,購入日 from order_data as m
where 購入日 in
(select min(購入日)as 初回 from order_data as s group by ユーザー名)

このように「＝」ではなく「in」と書いてあげると正しい結果が表示されるはずです。

他にもJOINを使ったり、MySQLのバージョンが新しければ他の書き方もできますが、ここではこのくらいにしてまた後日。