Oracle SQL -- Analytic functions OVER a group?

Question

My table:

ID   NUM   VAL
1    1     Hello
1    2     Goodbye
2    2     Hey
2    4     What's up?
3    5     See you

If I want to return the max number for each ID, it's really nice and clean:

SELECT MAX(NUM) FROM table GROUP BY (ID)

But what if I want to grab the value associated with the max of each number for each ID?

Why can't I do:

SELECT MAX(NUM) OVER (ORDER BY NUM) FROM table GROUP BY (ID) 

Why is that an error? I'd like to have this select grouped by ID, rather than partitioning separately for each window...

EDIT: The error is "not a GROUP BY expression".

Answer 1

You could probably use the MAX() KEEP(DENSE_RANK LAST...) function:

with sample_data as (
  select 1 id, 1 num, 'Hello' val from dual union all
  select 1 id, 2 num, 'Goodbye' val from dual union all
  select 2 id, 2 num, 'Hey' val from dual union all
  select 2 id, 4 num, 'What''s up?' val from dual union all
  select 3 id, 5 num, 'See you' val from dual)
select id, max(num), max(val) keep (dense_rank last order by num)
from sample_data
group by id;