SQLで、各区分の中における最大値となるIDが抽出できない

Question

Google Cloud Platformを使っている者です。
列がID(重複はしていないです), part(病名), a(数値)で構成されているテーブルから、
病名ごとの各区分の中での最大値とそのIDを抽出したいのですが、下記を実行すると、

select part,round(max(a),0) as MAXVALUEofA, ID
from high-territory-344913.practice220323.medical
group by part

SELECT list expression references column ID which is neither grouped nor aggregated at [1:45]
のエラーが出てしまい、実行できません。

しかし、select句からIDを除くと、問題なく実行できます。
select part,round(max(a),0) as MAXVALUEofA
from high-territory-344913.practice220323.medical
group by part

ご存知の方おられましたら、教えていただけますでしょうか。

Answer 1

Google Cloud Platformは使ったことがないのですが、
OracleやSQL Serverなどでも、
group byのキーに指定してない列はselect句で指定することができませんでした。
Google Cloud Platformも、エラーメッセージに出力されている通り同じなのだと思います。

Window関数などを使ってみると良いかもしれません。
(以下、sqlite3で試した結果ですが、Google Cloud PlatformでWindow関数が使えなかったり、書き方が違ったりするかもしれません・・雰囲気だけ見てください・・)

create table medical (
    ID integer,
    part text,
    a integer,
    primary key (ID)
);

insert into medical values (01, '病名A', 11);
insert into medical values (02, '病名A', 12);
insert into medical values (03, '病名A', 13);
insert into medical values (04, '病名B', 24);
insert into medical values (05, '病名B', 23);
insert into medical values (06, '病名B', 22);
insert into medical values (07, '病名B', 21);
insert into medical values (08, '病名C', 33);
insert into medical values (09, '病名C', 33);
insert into medical values (10, '病名C', 32);
insert into medical values (11, '病名C', 31);

select part, maxa, id
from (
    select id,
        part,
        a,
        max(a) over(partition by part) as maxa,
        row_number() over(partition by part order by a desc, id) num
    from medical
)
where a = maxa
and num = 1;

-- part|maxa|id
-- 病名A|13|3
-- 病名B|24|4
-- 病名C|33|8

Comments

[chanoma]

なるほどですね、over partitionを用いて集計する方法があったのですね。
学習中でして参考になりました。ありがとうございます。