Find Maximal Value of other Rows per Group

Question

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

;

Gurwinder Singh · Accepted Answer · 2017-04-18T19:56:11.110

4

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.

Demo - http://rextester.com/SDXVF13962

edited Apr 18 '17 at 19:56

answered Apr 16 '17 at 11:08

Gurwinder Singh

38,557
6
51
76

1

Perfect solution. I was stuck on it for a while and was about to tell OP that it can't be done in a single query. Thats the beauty of SO, you always learn something new. – Utsav Apr 16 '17 at 11:23
Nice solution for positive `ID`s. – Marmite Bomber Apr 16 '17 at 16:43
@MarmiteBomber - I assumed that, yes. – Gurwinder Singh Apr 16 '17 at 19:49
@GurV could this limitation somehow be lifted, to give a solution for negative numbers as well? – Marmite Bomber Apr 18 '17 at 05:57
@MarmiteBomber - Updated the answer. Please check – Gurwinder Singh Apr 18 '17 at 19:32

score 1 · Answer 2 · answered Apr 17 '17 at 09:52

1

SELECT GRP_ID,ID, (SELECT Max(ID)  FROM TEST A WHERE A.ROWID<>B.ROWID AND A.GRP_ID=B.GRP_ID) maX_ID FROM TEST B;

Got the expected result with Co-Related Query ! Hope this helps .

answered Apr 17 '17 at 09:52

Aspirant

2,238
9
31
43

Find Maximal Value of other Rows per Group

2 Answers2