create table Table743(
Code number(2),
Col1 number(3),
Col2 number(3),
Col3 number(3),
Sort1 number(1),
Sort2 number(1));
insert into Table743 values(10,100,120,130,1,1);
insert into Table743 values(10,110,100,140,1,2);
insert into Table743 values(10,130, 90, 20,2,1);
insert into Table743 values(10,120, 80,130,2,2);
insert into Table743 values(20, 10, 12, 13,1,1);
insert into Table743 values(20, 13, 9, 20,2,1);
insert into Table743 values(20, 12, 8, 11,2,2);
insert into Table743 values(20, 11, 10, 14,3,3);
commit;
--■■■分析関数を使う方法■■■
select distinct Code,
sum(Col1) over(partition by Code) as Col1Sum,
Last_Value(Col2) over(partition by Code order by Sort1,Sort2
Rows between Unbounded Preceding and Unbounded Following) as Col2LastVal,
Last_Value(Col3) over(partition by Code order by Sort1,Sort2
Rows between Unbounded Preceding and Unbounded Following) as Col3LastVal
from Table743
order by Code;
--■■■keepを使う方法■■■
select Code,sum(Col1) as Col1Sum,
max(Col2) Keep(Dense_Rank Last order by Sort1,Sort2) as Col2LastVal,
max(Col3) Keep(Dense_Rank Last order by Sort1,Sort2) as Col3LastVal
from Table743
group by Code
order by Code;