GROUP BYとMAX関数を使って最新レコードを取得する方法

SQLで各ユーザーの最新レコードの日付を取得するには、いくつかの方法があります。ここでは、最も一般的な2つの方法を紹介します。

方法1：GROUP BYとMAX関数を使う

この方法は、まずユーザーIDでグループ化し、各グループの中で最大の日付を取得する方法です。

SELECT user_id, MAX(date) AS latest_date
FROM table
GROUP BY user_id;

例

以下のテーブル users がある場合：

| user_id | date       |
|---------|------------|
| 1       | 2023-01-01 |
| 1       | 2023-02-01 |
| 2       | 2023-03-01 |
| 2       | 2023-04-01 |

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

| user_id | latest_date |
|---------|------------|
| 1       | 2023-02-01 |
| 2       | 2023-04-01 |

方法2：サブクエリを使う

この方法は、まず各ユーザーの最新レコードのIDを取得し、そのIDを使ってレコードを取得する方法です。

SELECT *
FROM table
WHERE id IN (
  SELECT id
  FROM table
  GROUP BY user_id
  ORDER BY date DESC
  LIMIT 1
);

| user_id | date       | id |
|---------|------------|----|
| 1       | 2023-01-01 | 1 |
| 1       | 2023-02-01 | 2 |
| 2       | 2023-03-01 | 3 |
| 2       | 2023-04-01 | 4 |

| user_id | date       | id |
|---------|------------|----|
| 1       | 2023-02-01 | 2 |
| 2       | 2023-04-01 | 4 |

• 方法1は、シンプルな方法です。ただし、テーブルに大量のデータがある場合は、処理速度が遅くなる可能性があります。
• 方法2は、処理速度が速い方法です。ただし、サブクエリを使うため、コードが複雑になります。

上記の2つの方法以外にも、以下のような方法があります。

• ウィンドウ関数を使う
• CTEを使う

補足

• 上記のクエリは、MySQL、PostgreSQL、Oracleなどの主要なデータベースで動作します。
• 日付カラムの名前は、環境に合わせて変更してください。

SELECT user_id, MAX(date) AS latest_date
FROM users
GROUP BY user_id;

SELECT *
FROM users
WHERE id IN (
  SELECT id
  FROM users
  GROUP BY user_id
  ORDER BY date DESC
  LIMIT 1
);

実行環境

上記のサンプルコードは、以下の環境で実行できます。

• MySQL
• PostgreSQL
• Oracle

実行方法

1. データベースに接続します。
2. サンプルコードを実行します。
3. 結果を確認します。

結果

| user_id | latest_date |
|---------|------------|
| 1       | 2023-02-01 |
| 2       | 2023-04-01 |

• 上記のサンプルコードは、テーブル users が存在することを前提としています。
• テーブル users のカラム構成は、以下の通りです。

CREATE TABLE users (
  user_id INT,
  date DATE
);

SQLで各ユーザーの最新レコードの日付を取得するその他の方法

ウィンドウ関数を使うと、各ユーザーの最新レコードの日付を簡単に取得することができます。

SELECT user_id, date,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date DESC) AS rn
FROM users;

WHERE rn = 1;

| user_id | date       |
|---------|------------|
| 1       | 2023-01-01 |
| 1       | 2023-02-01 |
| 2       | 2023-03-01 |
| 2       | 2023-04-01 |

| user_id | date       | rn |
|---------|------------|----|
| 1       | 2023-02-01 | 1 |
| 2       | 2023-04-01 | 1 |

CTEを使うと、複雑なクエリを分かりやすく記述することができます。

WITH latest AS (
  SELECT user_id, date,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date DESC) AS rn
  FROM users
)
SELECT *
FROM latest
WHERE rn = 1;

| user_id | date       |
|---------|------------|
| 1       | 2023-01-01 |
| 1       | 2023-02-01 |
| 2       | 2023-03-01 |
| 2       | 2023-04-01 |

| user_id | date       | rn |
|---------|------------|----|
| 1       | 2023-02-01 | 1 |
| 2       | 2023-04-01 | 1 |