【SQL】グループ中の最大値のレコードを抽出する方法

2015/11/11 2019/03/01

SQLのGROUP BY句とMAX()を利用して、グループ化したレコード中から最大値をもつレコードを抽出する方法を備忘録としてポストします。

以下の様な社員マスタ、出張先マスタ、出張管理テーブルがあるとします。
出張管理テーブルから、各社員の最新の出張先を抽出したい場合のSQLを考えます。

GROUP BY 及び MAX() でグループ中の最大値を抽出する

レコード作成

[sql]
--
-- 社員マスタ
--
CREATE TABLE users(
id INT UNSIGNED AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
PRIMARY KEY (id)
) TYPE = InnoDB DEFAULT CHARSET=utf8;

INSERT INTO users VALUES(null, '鈴木');
INSERT INTO users VALUES(null, '渡嘉敷');
INSERT INTO users VALUES(null, '夏目');
INSERT INTO users VALUES(null, '野々村');
INSERT INTO users VALUES(null, '泉');
INSERT INTO users VALUES(null, '虹野');

--
-- 出張先マスタ
--
CREATE TABLE places(
id INT UNSIGNED AUTO_INCREMENT,
place VARCHAR(255) NOT NULL,
PRIMARY KEY (id)
) TYPE = InnoDB DEFAULT CHARSET=utf8;

INSERT INTO places VALUES(null, '大阪');
INSERT INTO places VALUES(null, '東京');
INSERT INTO places VALUES(null, '福岡');
INSERT INTO places VALUES(null, '札幌');
INSERT INTO places VALUES(null, '沖縄');
INSERT INTO places VALUES(null, '石川');

--
-- 出張管理テーブル
--
CREATE TABLE trips(
id INT UNSIGNED AUTO_INCREMENT,
name_id INT NOT NULL,
place_id INT NOT NULL,
date DATE default NULL,
PRIMARY KEY (id)
) TYPE = InnoDB DEFAULT CHARSET=utf8;

INSERT INTO trips VALUES(null, 1, 5, '2015-6-15');
INSERT INTO trips VALUES(null, 2, 2, '2015-4-12');
INSERT INTO trips VALUES(null, 6, 3, '2015-9-10');
INSERT INTO trips VALUES(null, 4, 4, '2015-2-24');
INSERT INTO trips VALUES(null, 5, 1, '2015-8-23');
INSERT INTO trips VALUES(null, 3, 3, '2015-6-14');
INSERT INTO trips VALUES(null, 2, 5, '2015-9-22');
INSERT INTO trips VALUES(null, 5, 4, '2015-8-31');
INSERT INTO trips VALUES(null, 1, 6, '2015-1-19');
INSERT INTO trips VALUES(null, 3, 6, '2015-10-24');
INSERT INTO trips VALUES(null, 4, 2, '2015-5-27');
INSERT INTO trips VALUES(null, 6, 1, '2015-6-10');
[/sql]

社員マスタ (users)

出張先マスタ (places)

出張管理テーブル (trips)

SQLを考える

考え方としては、「社員ごと」でレコードを「グループ化」し日付の大きいレコードを抽出すれば良い、ということ。鈴木(user_id:1)で考えると、

[sql]
SELECT * FROM trips WHERE user_id = 1;
[/sql]

の内、id:1のレコードが対象となるようです。

tripsテーブルの内部IDが大きい方が最新と思いがちですが、そうでない場合も多々考えられるので、IDを比較するのではなく、日付を比較するのが吉。

これをSQLに落としこむと、以下のようになります。

[sql mark="10-18"]
SELECT
trip.id,
user.name,
place.place,
trip.date
FROM
trips AS trip
LEFT JOIN places AS place ON place.id = trip.place_id
LEFT JOIN users AS user ON user.id = trip.user_id
INNER JOIN(
SELECT
user_id AS clm1,
MAX(date) AS clm2
FROM
trips
GROUP BY
user_id
) AS table2 ON table2.clm2 = trip.date AND table2.clm1 = trip.user_id
ORDER BY trip.id ASC
[/sql]