1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37
| SQL> with t as (
select 1 as c1, 1 as c2 from dual union all
select 1 as c1, 3 as c2 from dual union all
select 2 as c1, 1 as c2 from dual union all
select 2 as c1, 2 as c2 from dual
)
select c1,
max(c2) over(partition by c1) as max_c2
from t
/
C1 MAX_C2
---------- ----------
1 3
1 3
2 2
2 2
SQL>
SQL>
SQL> with t as (
select 1 as c1, 1 as c2 from dual union all
select 1 as c1, 3 as c2 from dual union all
select 2 as c1, 1 as c2 from dual union all
select 2 as c1, 2 as c2 from dual
)
select c1, max(c2) as max_c2
from t
group by c1
/
C1 MAX_C2
---------- ----------
1 3
2 2
SQL> |