指定列の値が重複しているレコード単位から先頭１件のみを抽出する

The page you are currently viewing does not support the selected display language.

通常重複レコードから１件のみを抽出する場合、ぱっと頭に思いつくのは distinct だと思いますが、 distinct では重複列の指定とほかの取得したい列を同時に指定することはできません。 また、distinct は重複していないレコードも抽出対象に含まれてしまうので今回の要件とは一致しません。

タイトルだけだとやりたいことがいまいちわからないかと思いますので、サンプルレコードを例にしてみます。 やりたいことは以下のような抽出結果です。

これを以下のように取得します。

ID 1 と ID 2 は重複していないので取得対象外です。 ID 3 と ID 4 は重複しているのでその中から１件取得しています。ただ今回取得するのは DB 格納順の先頭レコードではなく、 さらに「Name に @ マークが含まれている」「Name の文字数が少ない」という条件で優先的に並べてその先頭１行が欲しいものとします。

上記の条件は以下のような SQL で取得することが可能です。 ただ、サブクエリなどを使用しておりパフォーマンスまでは確認していません。

select
  *
from
  (select *, row_number() over(order by ID asc) as RowNum from SampleTable) tableMain
where tableMain.RowNum =
(
  select
    top(1) RowNum
  from
    (select *, row_number() over(order by ID asc) as RowNum from SampleTable) tableSub
  where
        tableMain.ID = tableSub.ID
    and exists ( 
      select
        *
      from
        SampleTable
      where
        tableSub.ID = ID
      having
        count(*) >= 2
    )
  order by
    case when charindex(N'@', Name) >= 1 then 0 else 1 end, len(Name)
)

重複行は having と count でチェックしています。group by は使わず外からチェック対象のキー列、ここでは ID 列を where で絞っています。

先頭１行を取得するための優先順位は order by で指定します。ここでは charindex と len で「指定文字を含むか」「文字列の長さ」を確認しています。 後は top で各々の重複レコードの先頭１行を取得すればよいです。

本来これだけでいいのですが、「ID:4, Name:名前４@example.com」のように完全に同じ値を持つレコードが２つ以上いるとどうしても２件取得してしまいます。 そのため row_number で行ごとに識別できるように付加し全く同じ値のレコードでも１件だけ取得できるようにしています。 もし完全に同じレコードが存在しないことが判明しているのであれば row_number は付加せずに既存のレコードの値同士でチェックしたほうがパフォーマンスはいいかと思います。

重複チェック対象のキーが複数ある場合は各々のキー列の数を増やせばいいです。 例えば以下のレコードがあるとします。重複チェックする列は「ID1」「ID2」です。

取得結果は以下のようになります。

SQL は以下のようになります。キー列を指定していた部分をそのまま増やせばいいです。

select
  *
from
  (select *, row_number() over(order by ID1 asc, ID2 asc) as RowNum from SampleTable) tableMain
where tableMain.RowNum =
(
  select
    top(1) RowNum
  from
    (select *, row_number() over(order by ID1 asc, ID2 asc) as RowNum from SampleTable) tableSub
  where
        tableMain.ID1 = tableSub.ID1
    and tableMain.ID2 = tableSub.ID2
    and exists ( 
      select
        *
      from
        SampleTable
      where
            tableSub.ID1 = ID1
        and tableSub.ID2 = ID2
      having
        count(*) >= 2
    )
  order by
    case when charindex(N'@', Name) >= 1 then 0 else 1 end, len(Name)
)