Oracle SQL -- Analytic functions OVER a group?

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_datagroup by id;

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 xwhere (id,num) in  (     select id, max(num)      from x      group by id  )

Output:

ID  NUM VAL1   2   Goodbye2   4   What's up3   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_selectfrom 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

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_dataAS(  SELECT    ROW_NUMBER() OVER (PARTITION BY id ORDER BY num DESC) AS sequence_id,    *  FROM    yourTable)SELECT  *FROM  sequenced_dataWHERE  sequence_id = 1

EDIT

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

SELECT  *FROM  yourTableWHERE  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_queryWHERE  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 BYFROM  yourTableGROUP 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