I have a simple table with values (ID) in groups (GRP_ID).
create table tst as
select 1 grp_id, 1 id from dual union all
select 1 grp_id, 1 id from dual union all
select 1 grp_id, 2 id from dual union all
select 2 grp_id, 1 id from dual union all
select 2 grp_id, 2 id from dual union all
select 2 grp_id, 2 id from dual union all
select 3 grp_id, 3 id from dual;
It is straightforward to find a maximum value per group using analytical functions.
select grp_id, id,
max(id) over (partition by grp_id) max_grp
from tst
order by 1,2;
GRP_ID ID MAX_GRP
---------- ---------- ----------
1 1 2
1 1 2
1 2 2
2 1 2
2 2 2
2 2 2
3 3 3
But the goal is to find the maximum value excluding the value of the current row.
This is the expected result (column MAX_OTHER_ID):
GRP_ID ID MAX_GRP MAX_OTHER_ID
---------- ---------- ---------- ------------
1 1 2 2
1 1 2 2
1 2 2 1
2 1 2 2
2 2 2 2
2 2 2 2
3 3 3
Note that in the GRP_ID = 2 a tie on the MAX value exists, so the MAX_OTHER_ID remains the same.
I did manage this two step solution, but I'm wondering if there is a more straightforward and simple solution.
with max1 as (
select grp_id, id,
row_number() over (partition by grp_id order by id desc) rn
from tst
)
select GRP_ID, ID,
case when rn = 1 /* MAX row per group */ then
max(decode(rn,1,to_number(null),id)) over (partition by grp_id)
else
max(id) over (partition by grp_id)
end as max_other_id
from max1
order by 1,2
;
I wish the window functions supported multiple range specifications something like:
max(id) over (
partition by grp_id
order by id
range between unbounded preceding and 1 preceding
or range between 1 following and unbounded following
)
But unfortunately they don't.
As a workaround, you can avoid subqueries and CTEs using the function twice on the different ranges and call coalesce on that.
select grp_id,
id,
coalesce(
max(id) over (
partition by grp_id
order by id
range between 1 following and unbounded following
)
, max(id) over (
partition by grp_id
order by id
range between unbounded preceding and 1 preceding
)
) max_grp
from tst
order by 1,
2
Coalesce works out of the box because of the ordering clause as the result of the window function call will be either the max in the given window or a null value.