CODEWITHSUNDEEP
oracle-databasewindow-functions
Rabers
Rabers

Reputation: 45

Oracle Max Over Partition By Excluding Current Row

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

Upvotes: 1

Views: 53

Answers (3)

Chris Saxon
Chris Saxon

Reputation: 9875

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

Upvotes: 3

MT0
MT0

Reputation: 168470

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

Upvotes: 3

Barmar
Barmar

Reputation: 782158

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

Upvotes: 0

Related Questions