oracle-databasewindow-functions

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


Solution

  • 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