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;

Answer 2

When you use windowing function, you don't need to use GROUP BY anymore, this would suffice:

select id, 
     max(num) over(partition by id) 
from x 

Actually you can get the result without using windowing function:

select * 
from x
where (id,num) in
  (
     select id, max(num) 
     from x 
     group by id
  )

Output:

ID  NUM VAL
1   2   Goodbye
2   4   What's up
3   5   SEE YOU

http://www.sqlfiddle.com/#!4/a9a07/7

---

If you want to use windowing function, you might do this:

select id, val, 
     case when num =  max(num) over(partition by id) then
        1
     else
        0
     end as to_select
from x 
where to_select = 1

Or this:

select id, val 
from x 
where num =  max(num) over(partition by id) 

But since it's not allowed to do those, you have to do this:

with list as
(
  select id, val, 
     case when num =  max(num) over(partition by id) then
        1
     else
        0
     end as to_select
  from x
)
select * 
from list 
where to_select = 1

http://www.sqlfiddle.com/#!4/a9a07/19