Search notes:

SQL: GROUP BY / KEEP … DENSE_RANK

Some aggregate functions can be followed by the keywords KEEP and DENSE_RANK:

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

Such a construct returns the value of expr whose record within a group has the lowest value for anotherExpr.

I hope the following example demonstrates this behavior.

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

For each group, we want to select the maximum value of VAL and the corresponding values (of the same record) for TXT and NUM. For example, in group B, the maximum value of ID is 7 and the corresponding value for TXT and NUM are de and 28.

First, we need a table:

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

Github repository about-SQL, path: /select/group-by/keep-dense_rank/create-table.sql

And some data in the table.

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);

Github repository about-SQL, path: /select/group-by/keep-dense_rank/insert-data.sql

Then, we're ready to select the data as outlined above:

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
;

Github repository about-SQL, path: /select/group-by/keep-dense_rank/select-data.sql

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

SQL: GROUP BY / KEEP … DENSE_RANK

See also