SQL: GROUP BY / KEEP … DENSE_RANK

max(expr) KEEP (DENSE_RANK first order by anotherExpr).

Here's the data. The value of ID groups the data into three groups, A, B and C.

create table some_data (
   id  char    (1) not null,
   val number  (1) not null,
   txt varchar2(4) not null,
   num number  (2) not null
);

insert into some_data values ('A', 5, 'fgh' , 14);
insert into some_data values ('A', 4, 'lmn' , 22);
insert into some_data values ('A', 9, 'abc' , 19);

insert into some_data values ('B', 2, 'ijk' , 35);
insert into some_data values ('B', 3, 'op'  , 15);
insert into some_data values ('B', 7, 'de'  , 28);
insert into some_data values ('B', 6, 'xyz' , 47);

insert into some_data values ('C', 4, 'tuvw', 33);
insert into some_data values ('C', 2, 'qrs' , 37);

select
   id,
   min(val) keep (dense_rank first order by val desc) max_val_for_id       ,
   min(txt) keep (dense_rank first order by val desc) txt_for_max_val_in_id,
   min(num) keep (dense_rank first order by val desc) num_for_max_val_in_id
from
   some_data
group by
   id
order by
   id
;

The result set (here displayed in Oracle SQL Developer):

create table tq84_a (
  a  varchar2(10),
  b  number
);

insert into tq84_a values ('a', 1);
insert into tq84_a values ('b', 2);
insert into tq84_a values ('a', 4);
insert into tq84_a values ('b', 3);

select
  max(a) keep (dense_rank first order by b desc)  a,
  max(b) keep (dense_rank first order by b desc)  b
from
  tq84_a
;

drop table tq84_a purge;

create table tq84_group_by_vs_over (
  a varchar2(3),
  b number
);


insert into tq84_group_by_vs_over values ('foo', 42);
insert into tq84_group_by_vs_over values ('foo', 10);
insert into tq84_group_by_vs_over values ('foo', 13);

insert into tq84_group_by_vs_over values ('bar',  7);
insert into tq84_group_by_vs_over values ('bar', 39);
insert into tq84_group_by_vs_over values ('bar', 88);

insert into tq84_group_by_vs_over values ('baz', 21);

----------------------------------------------------

select
  max(b) keep (dense_rank first order by b desc) over (partition by a) max_b_for_a,
  --                                             ^^^^^^^^^^^^^^^^^^^^^
  --                                             Missing in 2nd query
  a
from tq84_group_by_vs_over
;

----------------------------------------------------

select
  max(b) keep (dense_rank first order by b desc)  max_b_for_a,
  a
from tq84_group_by_vs_over
  group by a -- <<< Missing in 1st query
;

----------------------------------------------------

drop table tq84_group_by_vs_over purge;