oracle keep

语法：

min | max(column1) keep (dense_rank first | last order by column2) over (partion by column3);
--返回按照column3分组后，按照column2排序的结果集中第一个或最后一个最小值或最大值column1。

 

 

创建测试数据：

create table test(
id     number,
a      number,
b      number
);

insert into test values (1,111,1 );
insert into test values (1,222,1 );
insert into test values (1,333,1 );
insert into test values (1,555,3 );
insert into test values (1,666,3 );
insert into test values (2,111,1 );
insert into test values (2,222,1 );
insert into test values (2,333,2 );
insert into test values (2,555,2 );
commit;

 示例1：

select max(a)keep(dense_rank first order by b) from test;

结果：

示例2：

select max(a)keep(dense_rank last order by b) from test;

结果：

示例3：

SELECT MAX(a) keep(dense_rank FIRST ORDER BY b) over(PARTITION BY id), t.* FROM test t;

结果：

示例4：

SELECT MAX(a) keep(dense_rank last ORDER BY b) over(PARTITION BY id), t.* FROM test t;

结果：