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
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
Share Improve this question edited Feb 18 at 0:12 MT0 168k12 gold badges66 silver badges127 bronze badges asked Feb 17 at 23:21 RabersRabers 451 silver badge9 bronze badges 1- Why did you remove the example data from the question. It's harder to understand now. – Barmar Commented Feb 17 at 23:41
3 Answers
Reset to default 3You 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
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 windowexclude groupomit rows with the same value for the sort columnsexclude tiesomit other rows with the same value for the sort columns; include the current row
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