SQLであるカラムの文字列でグループ分けして、最大行を取得したい。

Question

発生している問題

SQLで以下のテーブルがあります。
KEY1の先頭2桁の文字でグループ分けして、
グループ分けした中からKEY1,KEY2,KEY3が最大の行を取得するSQLがわかりません。

KEY1,KEY2,KEY3
"A1191028000001" , "00002" , "0"　⇒取得したい行
"B1191028000001" , "00001" , "1"
"B1191028000002" , "00001" , "1"
"B1191028000003" , "00002" , "0"
"B1191028000003" , "00002" , "1"　⇒取得したい行

上記のテーブルを基にSQLを実行した場合、以下の結果が得られるSQLを教えてください。
KEY1,KEY2,KEY3
"A1191028000001" , "00002" , "0"
"B1191028000003" , "00002" , "1"

Comments

[yambejp]

「最大の行」の定義がわかりません
SQLにおける順番は最大/最小とは無関係です
KEY1同士を文字列として比較していいのでしょうか？

[g1081828]

言葉足らずで申し訳ないです。
文字列として比較して下さい。

[yambejp]

つまりKEY2やKEY3の値は比較の対象ではないのですね？

[g1081828]

質問が悪かったのでテーブル内用を更新しました。
やりたいこととしてまず、KEY1で文字列を比較し、同じ値であればKEY2を文字列で比較。
KEY１もKEY2も同じ値であれば、KEY3を文字列で比較したいです。

[Orlofsky]

テーブル設計の入門で習うことですが、ひとつのカラムにはひとつの意味しか持たせない、って掟があります。
> KEY1の先頭2桁の文字でグループ分けして
であれば、KEY1の先頭2桁と3桁目以降を別のカラムに分けます。

KEY1にインデックスが設定してあっても、SUBSTR する時にインデックスが使われず、テーブル全行のKEY1をSUBSTRして、...
データ量が増えるとどんどん遅くなります。

Answer 1

Key1, Key2, Key3を配列にして比較すると簡潔な記述にはなります。

with tbl as (
  select * from (values
     ('A1191028000001' , '00002' , '0')
    ,('B1191028000001' , '00001' , '1')
    ,('B1191028000002' , '00001' , '1')
    ,('B1191028000003' , '00002' , '0')
    ,('B1191028000003' , '00002' , '1')
  ) as tbl(Key1, Key2, Key3)
)
select * from tbl t
where not exists(
        select 1 from tbl 
        where key1 like substr(t.key1, 1, 2) || '%'
          and array[key1, key2, key3]::text[] > array[t.key1, t.key2, t.key3]::text[]
      )

但し、(key1, key2, key)のGinインデックスを持たない場合は、性能が出せないかもしれません。
その場合は、yambejpさんの条件のようにandとorで繋げる必要があります

Answer 2

Postgreの関数は詳しくないので適当に読み替えてみてください

create table tbl(KEY1 varchar(20),KEY2 varchar(10),KEY3 varchar(10));
insert into tbl values
('A1191028000001','00002','0'),
('B1191028000001','00001','1'),
('B1191028000002','00001','1'),
('B1191028000003','00002','1'),
('C1191028000001','00002','1'),
('C1191028000002','00002','1');


select * from tbl as t1
where not exists
(select 1 from tbl where
 substr(KEY1,1,2)=substr(t1.KEY1,1,2) and
 KEY1>t1.KEY1
);

調整

• KEY1を比較
• 同じならKEY2を比較
• 同じならKEY3を比較
• すべて同じなら１個だけ

create table tbl(KEY1 varchar(20),KEY2 varchar(10),KEY3 varchar(10));
insert into tbl values
('A1191028000001','00002','0'),
('B1191028000001','00001','1'),
('B1191028000002','00001','1'),
('B1191028000003','00002','0'),
('B1191028000003','00002','1'),
('C1191028000001','00002','1'),
('C1191028000002','00001','1'),
('C1191028000002','00002','0'),
('D1191028000001','00001','0'),
('D1191028000001','00001','0');


select distinct * from tbl as t1
where not exists
(select 1 from tbl where
  (substr(KEY1,1,2)=substr(t1.KEY1,1,2) and
    (KEY1>t1.KEY1 or
     KEY1=t1.KEY1 and KEY2>t1.KEY2 or
     KEY1=t1.KEY1 and KEY2=t1.KEY2 and KEY3>t1.KEY3 
    )
  )
);