特定のデータのSELECT

Question

userというテーブルの中から特定のデータ取得したいのですが、書き方がわかりません。

左から
id,name,age,phone,updateFlg
というカラムのテーブルで、以下のデータがテーブルの中には存在します。

1,佐藤,35,000-0000-0000,1
2,鈴木,32,111-1111-1111,1
3,田中,28,222-2222-2222,1
3,田中,28,222-2222-2121,2
4,高橋,22,333-3333-3333,1
5,伊藤,48,444-4444-4444,1
5,伊藤,48,444-4444-4242,2
5,伊藤,48,444-4444-4242,3
5,佐藤,45,555-5555-5555,1

このテーブルの、idとnameがダブっているものの中でupdateFlgが最大のものを全て取得したいです。

・基本的に全てのデータがほしい。
・idとnameのどちらもがダブっているものはupdateFlgが最大のものだけほしい。

つまり、1,2,4,5,8,9行目のデータを取得したいです。
どのようなSQL文を書けばできるでしょうか？

Comments

[Orlofsky]

同じSQLでもデータベースやそのバージョンによって方言が大きいですから、どのデータベースを使うのかを質問のタグで示したり、バージョンも明記した方が適切なコメントが付き易いです。

[Orlofsky]

SQLで使われるテーブルはCREATE TABLEを実行しなければなりません。テーブルのデータもINSERTされていなければなりません。
質問のテーブル定義は CREATE TABLE文に、テーブルのデータは INSER文で https://teratail.com/help/question-tips#questionTips3-7 の [コード] に変更できた方が適切なコメントが付き易いです。

Answer 1

名前は旧姓などのケースがあると思いますので、判断には使用していません。

相関副問合せ

select * from user
where updateflg=(
        select max(updateflg) from user t1 where id=user.id
      )

副問合せ

select * from user
where (id, updateflg) in (
        select id, max(updateflg) from user group by id
      )

インラインビューとの結合

select user.*
from user inner join (
      select id, max(updateflg) max_flg from user group by id
    ) t1
    on    user.id=t1.id and user.updateflg=t1.max_flg

追記

名前を判断に含めた場合を追加
考え方は何も変わっておらず、単にキー項目を増やしただけです。

相関副問合せ

select * from user
where updateflg=(
        select max(updateflg) from user t1 where id=user.id and name=user.name
      )

副問合せ

select * from user
where (id, name, updateflg) in (
        select id, name, max(updateflg) from user group by id, name
      )

インラインビューとの結合

select user.*
from user inner join (
      select id, name, max(updateflg) max_flg from user group by id, name
    ) t1
    on    user.id=t1.id and name=t1.name and user.updateflg=t1.max_flg

Comments

[alg]

質問文の「1,2,4,5,8,9行目のデータを取得したいです。」からは外れた結果になってしまいますが、本当に欲しかった結果はこちらなのでは？という気もしますね……。

[sazi]

外れてます？

[alg]

9行目「5,佐藤,45,555-5555-5555,1」が対象に含まれないようです。
http://sqlfiddle.com/#!4/27644/3

[sazi]

名前が変わってもupdateFlgがリセットされる仕様なのでしたか。見落としてました。
追記しておきました。
algさんも指摘されておりますが、updateFlgはid毎に加算される仕様にしないと、IDが重複した結果での取得になります。