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

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

Answer 2

If you're looking to get the rows which contain the values from MAX(num) GROUP BY id, this tends to be a common pattern...

WITH
  sequenced_data
AS
(
  SELECT
    ROW_NUMBER() OVER (PARTITION BY id ORDER BY num DESC) AS sequence_id,
    *
  FROM
    yourTable
)
SELECT
  *
FROM
  sequenced_data
WHERE
  sequence_id = 1

EDIT

I don't know if TeraData will allow this, but the logic seems to make sense...

SELECT
  *
FROM
  yourTable
WHERE
  num = MAX(num) OVER (PARTITION BY id)

Or maybe...

SELECT
  *
FROM
(
  SELECT
    *,
    MAX(num) OVER (PARTITION BY id) AS max_num_by_id
  FROM
    yourTable
)
  AS sub_query
WHERE
  num = max_num_by_id 

This is slightly different from my previous answer; if multiple records are tied with the same MAX(num), this will return all of them, the other answer will only ever return one.

EDIT

In your proposed SQL the error relates to the fact that the OVER() clause contains a field not in your GROUP BY. It's like trying to do this...

SELECT id, num FROM yourTable GROUP BY id

num is invalid, because there can be multiple values in that field for each row returned (with the rows returned being defined by GROUP BY id).

In the same way, you can't put num inside the OVER() clause.

SELECT

  id,

  MAX(num),                <-- Valid as it is an aggregate

  MAX(num)                 <-- still valid
  OVER(PARTITION BY id),   <-- Also valid, as id is in the GROUP BY

  MAX(num)                 <-- still valid
  OVER(PARTITION BY num)   <-- Not valid, as num is not in the GROUP BY

FROM
  yourTable
GROUP BY
  id

See this question for when you can't specify something in the OVER() clause, and an answer showing when (I think) you can: over-partition-by-question

Answer 3

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;