SQLで重複した要素を無視してselectを行いたい。この時、得点が最も高い行のみを抽出したい。

Question

前提・実現したいこと

下図右側のようなtableがあるとき、ここからユーザ名の重複を除外してselectを行いたいです。ただしこの時、得点が最も高い行のみを残し、他を除外するようにしたいです。
同じ得点・同じユーザ名の列が複数ある場合は、そのうち一つだけを残したいです。
どのようなSQL文を書けばよいですか？ご教授ください。

補足情報（FW/ツールのバージョンなど）

SQLite3を利用しています。

サンプル作成に使ったSQL文は以下の通りです。

create table Test (id INTEGER PRIMARY KEY AUTOINCREMENT,user_name TEXT NOT NULL, score INTEGER NOT NULL );
insert into Test(user_name, score) values ("A",10);
insert into Test(user_name, score) values ("B",5);
insert into Test(user_name, score) values ("C",20);
insert into Test(user_name, score) values ("A",15);
insert into Test(user_name, score) values ("A",5);

ユーザ名のTableはありません。

Comments

[sousuke]

tableのcreate文やサンプルデータのinsert文などあると回答しやすいです。
ユーザーのテーブルはないんですか？

[nemumi-nomaki]

ご指摘ありがとうございます。SQL文を追加いたしました。ユーザーのテーブルはありません。

Answer 1

分析関数のRANK()が使えますね。

select *
from (
   select *
        , rank() over(partition by user_name order by score desc, id) as rnk
   from test
) tmp
where rnk=1

Comments

[nemumi-nomaki]

互換性とパフォーマンスを考慮して、ベストアンサーとさせていただきました。ご回答ありがとうございました。

Answer 2

これで取れると思います。

select *
from Test
group by user_name
having max(score)

Comments

[sazi]

havingが条件になっていないし、そもそもgroup by でselect * はエラーです。

[htsign]

おかしいですね。
$ sqlite3 -version
3.22.0 2018-01-22 18:45:57 0c55d179733b46d8d0ba4d88e01a25e10677046ee3da1d5b1581e86726f2alt1
この環境でローカルにデータ作って試した結果を確認しております。何か間違っているんでしょうか？

[sazi]

それは失礼しました。逆に私の手元には無いのでpostgresで確認しています。
having max(socore) とするとmaxの行のみになるんですか？
その場合同点ではどうなりますか？

[sazi]

group by 指定していない項目は適当（先頭とか最後とか）に表示されるんじゃないでしょうか？

[htsign]

試してみました。

> その場合同点ではどうなりますか？
最大の score を持つ行のうち（SQLite3の仕様を把握していないのでおそらくとしか言えませんが）insert順で最初のものが取れてきます。
これはダメですね。ご指摘いただいてありがとうございました。

> group by 指定していない項目は適当（先頭とか最後とか）に表示されるんじゃないでしょうか？
これは仰る意味がよく分かりませんでした。
「カラム順が user_name, id, score になるんじゃないか？」ということでしょうか？
そういう意味であれば、いいえ、テーブルに定義された順で取れます。
それとも「 group by 指定のないカラムの値はそれに適合した行のうち取れるものが分からないのではないか？（例えば最大の score が 80 だとして、score が 80 の行にそれぞれ id が 5 と 6 のものがあるとすれば、5 が取れるか 6 が取れるか分からないのではないか）」ということでしょうか？
それであれば上でお応えした通りです。

[sazi]

havingについては、「having 式」なので、max()のみだと、真偽判定では必ず値があるから全て真になっているのではないでしょうか。
つまり、having max()があっても無くても結果は変わらないのではないかと思われます。

[htsign]

変わりました。

sqlite> select * from Test;
1|A|10
2|B|5
3|C|20
4|A|15
5|A|5
6|A|20
7|A|20
8|C|10

このデータを持つテーブルがある中で

sqlite> select * from Test group by user_name;
7|A|20
2|B|5
8|C|10

sqlite> select * from Test group by user_name having max(score);
6|A|20
2|B|5
3|C|20

となります。

申し訳ないのですが、一度手元で確認していただけないでしょうか。
ほとんどのパッケージマネージャリポジトリには含まれているはずですから簡単にインストールできますし（バージョンが多少古いとかはあるかもしれませんが）それが面倒でもオンラインで簡易実行する環境もある昨今です。

https://sqliteonline.com/
https://extendsclass.com/sqlite-browser.html
など。

[sazi]

何となく動作が見えました。
having max(score)
だけでは意図通りでは無いという事も。
お節介は終わりにします。

[nemumi-nomaki]

すいません、私は全然動作が見えていないのですが、宜しければ教えていただけませんか。
なぜ条件式ではないhaving max(score)で最大値が取れるのでしょうか。（確認しましたがちゃんと取れました）
SQLite3の規格で動作が定まっていて、必ずそうなる、というものなのでしょうか。
それともたまたま上手くいっているだけで、動作は保証されていないのでしょうか。

[htsign]

ハッキリ言ってしまえば、確かにたまたまです。
書いたときは「あ、なんか動いた」程度の根拠でコメントしました。すみません。

ただ、改めて公式ドキュメントを漁ると、SELECT句の一節に "3. Generation of the set of result rows." という章 ( https://www.sqlite.org/lang_select.html#resultset ) があり、ここにHAVING句が使われた際の説明として "If the HAVING clause is an aggregate expression, it is evaluated across all rows in the group." と書かれています。
まさに今のシチュエーションになっています。一応 documented な動作になっているようです。

しかし @sazi さんがツッコミくださったように、確かに他のRDBではエラーになるクエリです。
個人開発でなく他のメンバーもいる場合などで、無難な実装を目指すのであれば避けたほうがよいかもしれませんね。

[nemumi-nomaki]

なるほど、SQLiteに限ればdocumentedな動作なんですね。ご説明ありがとうございます。

Answer 3

みんな大好きnot exists

CREATE TABLE Score
	(
     id integer primary key, 
     userName varchar(20), 
     userScore int
    );

INSERT INTO Score (id, userName, userScore)
VALUES (1, 'A', 10);

INSERT INTO Score (id, userName, userScore)
VALUES (2, 'B', 5);

INSERT INTO Score (id, userName, userScore)
VALUES (3, 'C', 20);

INSERT INTO Score (id, userName, userScore)
VALUES (4, 'A', 15);

INSERT INTO Score (id, userName, userScore)
VALUES (5, 'A', 5);

-- ↑これ用意してくれたら回答しやすいのでSQLの質問するなら用意したほうがいいです。

select *
from Score as A
where not exists(
  select 0
  from Score as B
  where B.userName=A.userName
    and B.userScore>A.userScore
)
order by A.userName

同名同点が追加されたので

こうかなあ

select *
from Score as A
where A.id = (
  select B.id
  from Score as B
  where B.userName=A.userName
  order by B.userScore desc,B.id
  limit 1
)
order by A.userName

Comments

[sazi]

同じ得点だと駄目ですね。

[sousuke]

まあそうです。最初はしていなかったですから