SQL ServerでCTEの最古1件だけをDELETEする方法｜TOP(1)とウィンドウ関数の実践テクニック

SQL ServerでCTEとウィンドウ関数を組み合わせると、重複行の検出までは簡単にできますが、「同一ユーザー内の最古の1件だけをDELETEしたい」となった瞬間につまずきがちです。この記事では、CTEの結果から最古1件（TOP(1)）だけを安全かつ確実に削除するための実践パターンを、サンプルコードとともに詳しく解説します。

問題の整理：CTEの結果から「最古の1件だけ」削除したい

今回想定するシナリオは次のようなものです。

テーブル：dbo.tblPBIGroup_CA
同一ユーザー（[User]）内で重複とみなせる行を判定したい
ウィンドウ関数で
- email_cnt > 1
- duplicate_yes = 1
という条件を満たす行を「重複」として抽出している
その重複行のうち、DateLoad が最も古い 1 件だけを DELETE したい

ところが、次のようなクエリは書けても…

SELECT TOP (1) *
FROM YourCte
WHERE email_cnt &gt; 1
  AND duplicate_yes = 1
ORDER BY DateLoad ASC;

「この TOP(1) の対象をそのまま DELETE したい」となると、構文や制約に引っかかって思うように書けないことがよくあります。

この記事では、SQL Serverでよく使われる３つのアプローチを中心に解説します。

解法A：CTEを2段階に分け、CTEから直接DELETEする方法
解法B：DELETE … FROM … JOIN で削除する方法
解法C：ROW_NUMBER()でユーザーごとに1件ずつ削除する方法

前提：SQL ServerでCTEからDELETEできる条件

SQL Serverでは、下記の条件を満たす場合に限り、CTEを対象に直接DELETEできます。

項目	内容
CTEが参照するテーブル	単一テーブルであること（ビューや別CTEを経由していても、最終的に1テーブルに更新が及ぶこと）
対象列	削除対象を一意に識別できる主キー（例：`Id`）が存在しているのが理想
DELETE対象	`DELETE FROM cte_alias` のように、CTEの別名を指定して削除可能

また、どの方法でも共通して重要なのは「削除対象を一意に識別できるキー列を必ず使う」ことです。SELECT * で行全体を比較して削除するような書き方は避け、Id などの主キー列を基準に DELETE しましょう。

サンプルデータのイメージ

説明を具体的にするために、簡略化したデータイメージを示します。

Id	User	DateLoad	AccessToAllLocations	Location
101	[email protected]	2024-01-01	1	Tokyo
102	[email protected]	2024-02-01	1	Osaka
103	[email protected]	2024-03-01	1	Nagoya
201	[email protected]	2024-01-15	1	Tokyo

たとえば、[User] = '[email protected]' について「AccessToAllLocations が複数、Location も複数ある」場合に duplicate_yes = 1 と判定し、さらに email_cnt > 1 の条件で重複行を抽出する、といったイメージです。

解法A：2段階CTEで最古1件を直接DELETEする

まずは、CTEから直接DELETEする王道パターンです。CTEを2段階に分けて考えます。

base CTE：ウィンドウ関数で email_cnt と duplicate_yes を計算
target CTE：条件を満たす行のうち、最古の1件（TOP(1)）だけを選択
DELETE：DELETE FROM target; で削除

サンプルSQL：CTEから直接DELETE

-- ① 判定用CTE
WITH base AS (
    SELECT
        t.*,
        COUNT(*) OVER (PARTITION BY [User]) AS email_cnt,
        CASE
            WHEN COUNT([AccessToAllLocations]) OVER (PARTITION BY [User]) &gt; 1
             AND COUNT([Location])             OVER (PARTITION BY [User]) &gt; 1
            THEN 1 ELSE 0
        END AS duplicate_yes
    FROM dbo.tblPBIGroup_CA AS t
),
-- ② 削除対象を1件に絞るCTE（最古の1件）
target AS (
    SELECT TOP (1) *
    FROM base
    WHERE email_cnt &gt; 1
      AND duplicate_yes = 1
    ORDER BY DateLoad ASC, Id ASC  -- DateLoadが同値の場合はIdでタイブレーク
)
-- ③ CTEを対象に直接DELETE
DELETE FROM target;

ポイント解説

TOP(1)とORDER BYは必ずCTE側に書く
- DELETE文そのものにORDER BYを直接書くことはできません。
- 削除順序を制御したい場合は、サブクエリまたはCTE側にTOP(1) ... ORDER BY ...を書くのが鉄則です。
タイブレーク用列（Id）の追加
- DateLoad が同じ日付の行が存在する場合、どれが削除されるかが曖昧になります。
- ORDER BY DateLoad ASC, Id ASC のように、主キー列を ORDER BY に含めておくと動作が安定します。
単一テーブル参照であること
- base CTE も target CTE も、最終的には dbo.tblPBIGroup_CA だけを参照しています。
- この条件が満たされているため、DELETE FROM target が有効です。

base CTEをカスタマイズするポイント

実際には、duplicate_yes の条件は要件によって変わります。例えば、「同一ユーザー・同一Locationの重複だけを対象にしたい」「特定の期間内の重複だけを消したい」などです。

その場合も、基本方針は同じです。

base CTEで「重複判定に必要な列・フラグ・集計結果」をすべて計算しておく
target CTEで「削除対象となる条件 + 最古の1件（TOP(1)）」に絞り込む
最後にDELETE FROM targetで削除

解法B：DELETE … FROM … JOINでキーを指定して削除する

次に、サブクエリ（またはCTE）で削除すべき主キーだけを抽出し、元テーブルとJOINしてDELETEするパターンです。いわゆる「Delete-Join」構文です。

サンプルSQL：DELETE JOINパターン

WITH base AS (
    SELECT
        t.Id,
        t.DateLoad,
        COUNT(*) OVER (PARTITION BY [User]) AS email_cnt,
        CASE
            WHEN COUNT([AccessToAllLocations]) OVER (PARTITION BY [User]) &gt; 1
             AND COUNT([Location])             OVER (PARTITION BY [User]) &gt; 1
            THEN 1 ELSE 0
        END AS duplicate_yes
    FROM dbo.tblPBIGroup_CA AS t
)
DELETE t
FROM dbo.tblPBIGroup_CA AS t
INNER JOIN (
    SELECT TOP (1) b.Id
    FROM base AS b
    WHERE b.email_cnt &gt; 1
      AND b.duplicate_yes = 1
    ORDER BY b.DateLoad ASC, b.Id ASC
) AS x
  ON x.Id = t.Id;

なぜ「キーだけ」を返すのか？

サブクエリ x では、あえて b.Id だけを返しています。これは次の理由によります。

DELETE対象の特定には主キーだけあれば十分だから
行全体をJOIN条件にしてしまうと、列値の違いでマッチしないリスクがあるから
シンプルなJOIN条件（ON x.Id = t.Id）にすることで、パフォーマンス・可読性が上がるから

DELETE JOINパターンのメリット・デメリット

観点	メリット	デメリット
柔軟性	サブクエリ側で複雑なロジックを組み立てやすい	CTEから直接DELETEするよりも行数が多いと少し読みにくいことも
テーブル構成	複数テーブルのJOIN結果を元にしても、最終的に1テーブルだけを削除対象にできる	JOINの書き方によっては意図しない行が巻き込まれるリスクがある
移植性	他のRDBMS（MySQL/PostgreSQL など）にも似た書き方があり、SQL Server以外に転用しやすい	SQL Server特有の構文（`DELETE t FROM ...`）に慣れていないと違和感がある

SQL Serverでは、「複雑なロジックはサブクエリかCTEで書き、DELETE対象はJOINで当てる」パターンを多用します。CTEから直接DELETEするのに抵抗がある場合は、この解法Bを採用すると良いでしょう。

解法C：ROW_NUMBER()でユーザーごとに1件ずつ削除する

もし要件が「ユーザーごとに最古の1件を削除したい（＝ユーザー単位で1件ずつ消す）」であれば、ROW_NUMBER() を使う方がシンプルなことが多いです。

ROW_NUMBER()で「各ユーザーの最古1件」だけDELETE

WITH c AS (
    SELECT
        t.*,
        ROW_NUMBER() OVER (
            PARTITION BY [User]
            ORDER BY DateLoad ASC, Id ASC
        ) AS rn
    FROM dbo.tblPBIGroup_CA AS t
    WHERE -- 必要ならここに「重複とみなす条件」を書く
          -- 例：AccessToAllLocations = 1 AND Location IS NOT NULL など
          1 = 1
)
DELETE FROM c
WHERE rn = 1;   -- 各Userの最古1件だけ削除

このパターンでは、「どの行が何番目のレコードか」をROW_NUMBERで決めてしまうため、後の処理が非常にわかりやすくなります。

rn = 1：最古の1件（または最優先の1件）だけを対象
rn > 1：重複分のみを削除したいとき
rn > 3：最新3件を残して古い履歴を削除したいとき、などの応用も簡単

今回のように、email_cnt や duplicate_yes の条件を組み合わせたい場合は、WHERE句か、あるいはROW_NUMBERのPARTITION/ORDER条件にうまく組み込んでいくのがおすすめです。

ORDER BYとDELETEの落とし穴：TOP(1)の使い方

「最古の1件だけを削除したい」という要件では、ORDER BYとTOP(1)の扱いが非常に重要です。特に次の点でつまずきやすいので注意しましょう。

DELETE文単体でのORDER BYは不可
- DELETE FROM dbo.tblPBIGroup_CA ORDER BY DateLoad ASC; のような書き方はできません。
- 必ずサブクエリやCTEにTOP(1) ... ORDER BY ...を書き、その結果を基にDELETEします。
ORDER BYを伴うTOP(1)は、SELECTの内部（サブクエリ/CTE）に置く
- SELECT TOP(1) ... ORDER BY ... を外側のDELETEに書くのではなく、あくまで「削除対象を特定するためのSELECT」に書くイメージです。
タイブレーク列を必ず指定する
- DateLoadだけでORDER BYした場合、同日付の行が複数あると、どれが消えるかが実装依存になります。
- 必ず主キーなどの一意な列（Idなど）を第二ソートキーに入れましょう。

NULL・予約語・COUNTの注意点

実務でよくハマりがちなポイントを、まとめて確認しておきます。

COUNT(列名) と COUNT(*) の違い

SQL Serverに限らず、COUNT(列名) は NULLをカウントしません。一方で COUNT(*) は NULLを含む行数そのものをカウントします。

「同一ユーザーの行数が2件以上あるかどうか」を判定したいだけなら、意図としては COUNT(*) の方が安全です。

式	NULLの扱い	典型的な用途
`COUNT(*)`	NULLも含めて行数としてカウント	純粋に「何行あるか」を知りたい場合
`COUNT(列名)`	NULLを除外してカウント	「特定列に値が入っている件数」を知りたい場合

もし AccessToAllLocations や Location にNULLが入り得る場合は、意図通りの件数になっているか注意深く確認してください。

NULLなDateLoadをどう扱うか

DateLoadがNULLのレコードがある場合、その扱いも決めておく必要があります。一般的には次のどちらかです。

NULLを「最古」と見なして先に削除する
NULLを「最後に」回したい（＝有効な日付があるレコードを優先的に削除したい）

後者の場合は、ORDER BYを工夫します。

ORDER BY
    CASE WHEN DateLoad IS NULL THEN 1 ELSE 0 END,
    DateLoad ASC,
    Id ASC;

こう書くことで、DateLoadがNULLの行は後ろへ回し、それ以外の行の中から最古を選ぶことができます。

[User], [Location] などの予約語っぽい列名

UserやLocationは、SQLの世界では予約語や組み込み関数と衝突しやすい名前です。SQL Serverでは角括弧で囲むクセをつけておくと安全です。

SELECT [User], [Location]
FROM dbo.tblPBIGroup_CA;

ダブルクォーテーション（"User"）でも良い場合がありますが、設定（QUOTED_IDENTIFIER）に依存するため、SQL Serverでは角括弧の方が無難です。

実務で使える「安全な削除手順」

SQL ServerでDELETEを実行するときに、個人的におすすめしている手順を紹介します。この流れをテンプレ化しておくと、誤削除のリスクをかなり減らせます。

1. まずはSELECTで削除対象を確認

いきなりDELETEせず、そのままDELETEに置き換えられる形のSELECTを書いて結果を確認します。

-- 確認用：本当に消したい行だけが出てくるかチェック
WITH base AS (
    SELECT
        t.*,
        COUNT(*) OVER (PARTITION BY [User]) AS email_cnt,
        CASE
            WHEN COUNT([AccessToAllLocations]) OVER (PARTITION BY [User]) &gt; 1
             AND COUNT([Location])             OVER (PARTITION BY [User]) &gt; 1
            THEN 1 ELSE 0
        END AS duplicate_yes
    FROM dbo.tblPBIGroup_CA AS t
),
target AS (
    SELECT TOP (1) *
    FROM base
    WHERE email_cnt &gt; 1
      AND duplicate_yes = 1
    ORDER BY DateLoad ASC, Id ASC
)
SELECT *
FROM target;

2. トランザクション内でDELETE + OUTPUTログ

問題なさそうであれば、そのままDELETEに書き換えてトランザクションで実行します。

BEGIN TRAN;

WITH base AS (
    SELECT
        t.*,
        COUNT(*) OVER (PARTITION BY [User]) AS email_cnt,
        CASE
            WHEN COUNT([AccessToAllLocations]) OVER (PARTITION BY [User]) &gt; 1
             AND COUNT([Location])             OVER (PARTITION BY [User]) &gt; 1
            THEN 1 ELSE 0
        END AS duplicate_yes
    FROM dbo.tblPBIGroup_CA AS t
),
target AS (
    SELECT TOP (1) *
    FROM base
    WHERE email_cnt &gt; 1
      AND duplicate_yes = 1
    ORDER BY DateLoad ASC, Id ASC
)
DELETE FROM target
OUTPUT DELETED.Id, DELETED.[User], DELETED.DateLoad;

-- 結果を確認して問題なければ
COMMIT;
-- もしおかしければ ROLLBACK; を実行

OUTPUT DELETED... で、削除された行をログテーブルに記録したり、そのまま画面で確認したりできます。これは復旧や原因調査の手がかりとして非常に有用です。

パフォーマンス・インデックス設計のポイント

CTEやウィンドウ関数を多用するクエリは、データ量が増えるほどパフォーマンスへの影響が大きくなります。代表的なチューニングポイントは次の通りです。

PARTITION BYに使う列にインデックスを貼る
- 今回の例では [User] 列が該当。
- CREATE INDEX IX_tblPBIGroup_CA_User_DateLoad ON dbo.tblPBIGroup_CA([User], DateLoad); のような複合インデックスが有効なことが多いです。
ORDER BY列もインデックスに含める
- DateLoad や Id をインデックスに含めることで、ソートのコストを抑えられます。
WHERE条件で絞れる場合は必ず絞る
- 「特定期間だけ」「特定のユーザーだけ」など、対象範囲を狭めることでウィンドウ関数の対象行も減り、全体のコストが下がります。

インデックス例

-- User と DateLoad がよく使われる場合のインデックス例
CREATE INDEX IX_tblPBIGroup_CA_User_DateLoad
ON dbo.tblPBIGroup_CA([User], DateLoad)
INCLUDE (AccessToAllLocations, [Location]);

INCLUDE 句に、SELECTでしか使わない列を含めておくことで、カバリングインデックスになり、テーブルへのアクセスを減らせるケースがあります。

応用パターン：最古N件を削除・最新N件を残すなど

今回のテーマは「最古の1件（TOP(1)）だけ削除」ですが、同じ考え方で次のような応用もできます。

ユーザーごとに最古N件を削除したい
ユーザーごとに最新N件を残し、それ以外を削除したい
特定の条件を満たす行についてのみ、最古1件だけを削除したい

最古3件を削除する例（ROW_NUMBER版）

WITH c AS (
    SELECT
        t.*,
        ROW_NUMBER() OVER (
            PARTITION BY [User]
            ORDER BY DateLoad ASC, Id ASC
        ) AS rn
    FROM dbo.tblPBIGroup_CA AS t
)
DELETE FROM c
WHERE rn &lt;= 3;   -- 各Userについて最古3件を削除

まとめ：CTE + DELETEで「狙った1件だけ」を安全に消す

この記事では、SQL ServerでCTEとウィンドウ関数を使い、最古の1件だけをDELETEする方法を具体的に解説しました。最後にポイントを整理します。

削除対象を一意に識別できるキー（主キーなど）を必ず使う
- Id などの主キーを基準にDELETEすることで、安全かつ明確な削除が可能になります。
解法A：2段階CTEで直接DELETE
- base CTEで判定列を計算し、target CTEでTOP(1) ... ORDER BY ...で最古1件に絞る。
- DELETE FROM target; で、CTEを直接削除できます。
解法B：DELETE … FROM … JOIN
- サブクエリ（またはCTE）で「削除すべきキーだけ」を抽出し、元テーブルとJOINしてDELETE。
- 複数テーブルを参照するような複雑なケースでも応用しやすい書き方です。
解法C：ROW_NUMBER()で番号を振って削除
- ユーザーごとに最古/最新N件を削除・残すといった要件にはROW_NUMBERが非常に有効です。
ORDER BYとNULL・COUNTの扱いに注意
- DELETE文単体にはORDER BYを書けないため、必ずサブクエリ/CTE側にTOP(1) ... ORDER BY ...を書きます。
- COUNT(列名)はNULLを数えない、DateLoadのNULLの扱いをどうするか…といった点も仕様として押さえておきましょう。
必ずSELECTで確認 → トランザクションでDELETE → OUTPUTでログ
- この流れをテンプレ化しておくと、本番環境での誤削除リスクを大きく減らせます。

CTEやウィンドウ関数は、一度「パターン」として身につけてしまえば、SQL Serverでのデータクレンジングや履歴管理を強力に支えてくれる武器になります。今回紹介した3つの解法をベースに、自分の環境・要件に合わせたカスタマイズパターンをいくつかストックしておくと、日々の運用や開発がぐっと楽になるはずです。

この記事を書いた人

佐藤健司

実務の現場で詰まりがちなポイントを地図にするITブログ「IT trip」を運営。Windows／Office（Teams・Excel）からSQL、サーバ運用、ガジェットまで、再現性のある手順と“なぜそうなるか”を丁寧に解説します。読んだらすぐ試せること、そして迷った人の次の一歩が見えることを大切にしています。

SQL ServerでCTEの最古1件だけをDELETEする方法｜TOP(1)とウィンドウ関数の実践テクニック

問題の整理：CTEの結果から「最古の1件だけ」削除したい

前提：SQL ServerでCTEからDELETEできる条件

サンプルデータのイメージ

解法A：2段階CTEで最古1件を直接DELETEする

サンプルSQL：CTEから直接DELETE

ポイント解説

base CTEをカスタマイズするポイント

解法B：DELETE … FROM … JOINでキーを指定して削除する

サンプルSQL：DELETE JOINパターン

なぜ「キーだけ」を返すのか？

DELETE JOINパターンのメリット・デメリット

解法C：ROW_NUMBER()でユーザーごとに1件ずつ削除する

ROW_NUMBER()で「各ユーザーの最古1件」だけDELETE

ORDER BYとDELETEの落とし穴：TOP(1)の使い方

NULL・予約語・COUNTの注意点

COUNT(列名) と COUNT(*) の違い

NULLなDateLoadをどう扱うか

[User], [Location] などの予約語っぽい列名

実務で使える「安全な削除手順」

1. まずはSELECTで削除対象を確認

2. トランザクション内でDELETE + OUTPUTログ

パフォーマンス・インデックス設計のポイント

インデックス例

応用パターン：最古N件を削除・最新N件を残すなど

最古3件を削除する例（ROW_NUMBER版）

最新5件を残してそれ以外を削除する例

まとめ：CTE + DELETEで「狙った1件だけ」を安全に消す

この記事を書いた人

コメント

コメントするコメントをキャンセル

SQL ServerでCTEの最古1件だけをDELETEする方法｜TOP(1)とウィンドウ関数の実践テクニック

問題の整理：CTEの結果から「最古の1件だけ」削除したい

前提：SQL ServerでCTEからDELETEできる条件

サンプルデータのイメージ

解法A：2段階CTEで最古1件を直接DELETEする

サンプルSQL：CTEから直接DELETE

ポイント解説

base CTEをカスタマイズするポイント

解法B：DELETE … FROM … JOINでキーを指定して削除する

サンプルSQL：DELETE JOINパターン

なぜ「キーだけ」を返すのか？

DELETE JOINパターンのメリット・デメリット

解法C：ROW_NUMBER()でユーザーごとに1件ずつ削除する

ROW_NUMBER()で「各ユーザーの最古1件」だけDELETE

ORDER BYとDELETEの落とし穴：TOP(1)の使い方

NULL・予約語・COUNTの注意点

COUNT(列名) と COUNT(*) の違い

NULLなDateLoadをどう扱うか

[User], [Location] などの予約語っぽい列名

実務で使える「安全な削除手順」

1. まずはSELECTで削除対象を確認

2. トランザクション内でDELETE + OUTPUTログ

パフォーマンス・インデックス設計のポイント

インデックス例

応用パターン：最古N件を削除・最新N件を残すなど

最古3件を削除する例（ROW_NUMBER版）

最新5件を残してそれ以外を削除する例

まとめ：CTE + DELETEで「狙った1件だけ」を安全に消す

この記事を書いた人

関連記事

コメント

コメントする コメントをキャンセル

コメントするコメントをキャンセル