グループ化の達人！MySQLでグループごとの最大値レコードを取得する4つのテクニック

2024-04-02

MySQLでグループ化された結果の各グループの最大値を持つレコードを取得する方法

SQLのGROUP BY句を使用してグループ化された結果の各グループの最大値を持つレコードを取得するには、いくつかの方法があります。

方法

MAX()関数とGROUP BY句を使用する

これは最も基本的な方法です。MAX()関数はグループ内の最大値を返し、GROUP BY句はグループを指定します。

SELECT
  group_column,
  MAX(value_column) AS max_value
FROM table_name
GROUP BY group_column;

例

次のテーブルがあるとします。

CREATE TABLE my_table (
  id INT,
  group_column VARCHAR(255),
  value_column INT
);

INSERT INTO my_table (id, group_column, value_column) VALUES
  (1, 'group1', 10),
  (2, 'group1', 20),
  (3, 'group2', 30),
  (4, 'group2', 40),
  (5, 'group3', 50);

上記のクエリを実行すると、次の結果が得られます。

group_column | max_value
-------------+----------
group1       | 20
group2       | 40
group3       | 50

GROUP BY句とサブクエリを使用する

この方法は、MAX()関数とGROUP BY句を使用してグループ内の最大値を取得し、それをサブクエリで結合します。

SELECT
  t1.group_column,
  t1.value_column
FROM table_name AS t1
INNER JOIN (
  SELECT
    group_column,
    MAX(value_column) AS max_value
  FROM table_name
  GROUP BY group_column
) AS t2
ON t1.group_column = t2.group_column
AND t1.value_column = t2.max_value;

ウィンドウ関数を使用する

MySQL 8.0以降では、ウィンドウ関数を使用してグループ内の最大値を取得できます。

SELECT
  group_column,
  value_column,
  MAX(value_column) OVER (PARTITION BY group_column) AS max_value
FROM table_name;

シンプルな方法で最大値を取得したい場合は、方法1を使用します。
MySQL 8.0以降を使用している場合は、方法3を使用すると、より簡潔なコードで最大値を取得できます。

方法1: MAX()関数とGROUP BY句を使用する

SELECT
  group_column,
  MAX(value_column) AS max_value
FROM my_table
GROUP BY group_column;

このクエリは、my_tableテーブルのgroup_columnごとにvalue_columnの最大値を取得します。

方法2: GROUP BY句とサブクエリを使用する

SELECT
  t1.group_column,
  t1.value_column
FROM my_table AS t1
INNER JOIN (
  SELECT
    group_column,
    MAX(value_column) AS max_value
  FROM my_table
  GROUP BY group_column
) AS t2
ON t1.group_column = t2.group_column
AND t1.value_column = t2.max_value;

方法3: ウィンドウ関数を使用する

SELECT
  group_column,
  value_column,
  MAX(value_column) OVER (PARTITION BY group_column) AS max_value
FROM my_table;

実行方法

これらのサンプルコードを実行するには、以下の手順が必要です。

MySQLに接続します。
サンプルコードをテキストエディタに保存します。
MySQLコマンドプロンプトまたはMySQL Workbenchを使用して、サンプルコードを実行します。

結果

group_column | max_value
-------------+----------
group1       | 20
group2       | 40
group3       | 50

上記の方法のいずれかを使用して、MySQLでグループ化された結果の各グループの最大値を持つレコードを取得できます。

他の方法

CASE式を使用する

SELECT
  group_column,
  value_column
FROM my_table
ORDER BY group_column, value_column DESC
GROUP BY group_column
LIMIT 1;

このクエリは、group_columnごとにvalue_columnの降順で結果を並べ替え、最初のレコードを取得します。

ユーザー定義関数を作成して、グループ内の最大値を取得することができます。

CREATE FUNCTION get_max_value(
  group_column VARCHAR(255),
  value_column INT
) RETURNS INT
BEGIN
  DECLARE max_value INT;

  SELECT MAX(value_column) INTO max_value
  FROM my_table
  WHERE group_column = group_column;

  RETURN max_value;
END;

SELECT
  group_column,
  get_max_value(group_column, value_column) AS max_value
FROM my_table
GROUP BY group_column;