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
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 |