Oracle SQL -- Analytic functions OVER a group?
greatest-n-per-group, oracle, sql
Solution
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;
Problem
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".