Oracle Max Over Partition By Excluding Current Row

Question

I have an issue to calculate the max() value over partition by where i want to exclude the current row each time. Assuming I have a table with ID, Group and Valbue. calculating max/min/etc. over partition by Group is strghait forward. however, If I want to calculate the group's MAX() for example excluding the ID's value. meaning, assuming I have 2 groups, with 3 IDs each. the MAX() for the first ID in the the first group would be between the other 2 IDs of that group (#2 and #3), the MAX() for the second ID for that group would be, again between the other two IDs (#1 and #3), and for the last ID of that group we'll have the max between value #1 and #2 (each row's value should be excluded from the aggregation of it's group)

Please assist

Why did you remove the example data from the question. It's harder to understand now. — Barmar
– Barmar, Commented Feb 17 at 23:41

MT0 · Accepted Answer · 2025-02-18 00:11:20Z

You can use the windowing clause of the analytic function to exclude the current row:

SELECT id,
       grp,
       value,
       COALESCE(
         MAX(value) OVER (
           PARTITION BY grp
           ORDER BY value
           ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING
         ),
         MAX(value) OVER (
           PARTITION BY grp
           ORDER BY value
           ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
         )
       ) AS max
FROM   table_name

Which, for the sample data:

CREATE TABLE table_name (id, grp, value) AS
SELECT 1, 1, 1 FROM DUAL UNION ALL
SELECT 2, 1, 2 FROM DUAL UNION ALL
SELECT 3, 1, 3 FROM DUAL UNION ALL
SELECT 4, 2, 4 FROM DUAL UNION ALL
SELECT 5, 2, 5 FROM DUAL UNION ALL
SELECT 6, 2, 5 FROM DUAL;

Outputs:

ID	GRP	VALUE	MAX
1	1	1	3
2	1	2	3
3	1	3	2
4	2	4	5
5	2	5	5
6	2	5	5

fiddle

Chris Saxon · Accepted Answer · 2025-02-18 12:16:19Z

3

From Oracle Database 21c the window frame has an exclude clause you can use to omit the current row from the calculation:

create table table_name (id, grp, value) as
  select 1, 1, 1 from dual union all
  select 2, 1, 2 from dual union all
  select 3, 1, 3 from dual union all
  select 4, 2, 4 from dual union all
  select 5, 2, 5 from dual union all
  select 6, 2, 5 from dual;

select t.*,
  max ( value ) over (
    partition by grp 
    order by id
      rows between unbounded preceding and unbounded following
      exclude current row
  ) max_except_current
from table_name t;

        ID        GRP      VALUE MAX_EXCEPT_CURRENT
---------- ---------- ---------- ------------------
         1          1          1                  3
         2          1          2                  3
         3          1          3                  2
         4          2          4                  5
         5          2          5                  5
         6          2          5                  5

Other options for this are:

exclude no others (default) include the whole window
exclude group omit rows with the same value for the sort columns
exclude ties omit other rows with the same value for the sort columns; include the current row

answered Feb 18 at 12:16

Chris Saxon

10.2k1 gold badge33 silver badges47 bronze badges

1 Comment

Rabers Feb 19 at 22:50

Hey, Thanks, I'm indeed aware of the EXCLUDE functionality buy unforuanetly, our version is a bit old so I can't use it

Barmar · Accepted Answer · 2025-02-17 23:31:37Z

0

Use a self-JOIN that excludes the same row.

SELECT t1.id, t1.group, t1.value, MAX(t2.value) AS Max_Excluded
FROM Your_Table AS t1
JOIN Your_Table AS t2 ON t1.group = t2.group AND t1.id <> t2.id
GROUP BY t1.id, t1.group, t1.value

answered Feb 17 at 23:31

Barmar

789k57 gold badges555 silver badges669 bronze badges

2 Comments

Rabers Feb 17 at 23:34

I've edited the question as it didn't come up as I thought. I'm using frame/window functions as I don't have a group by in my query

Barmar Feb 17 at 23:40

I'm not very adept at window functions, but I'm not sure they can do this kind of partitioning.

Collectives™ on Stack Overflow

Oracle Max Over Partition By Excluding Current Row

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related