Background information
The use of the aggregate function does not support the keep keyword. Example:
Sales table:
SQL> select * from criss_sales where dept_id = 'D02' order by sale_date ;
DEPT_ID SALE_DATE GOODS_TYPE SALE_CNT
------- ----------- ---------- -----------
D02 2014/3/6 G00 500
D02 2014/3/6 G01 430
D02 2014/4/8 G02 100
D02 2014/4/27 G01 300
D02 2014/5/2 G03 900
At this time, there is a new requirement, and you want to view the record with the earliest sales record time and the smallest sales volume in Department D02.
SQL> select
2 dept_id
3 ,min(sale_cnt)keep ( dense_rank first order by sale_date) min_early_date
4 from criss_sales
5 where dept_id = 'D02'
6 group by dept_id
7 ;
DEPT_ID MIN_EARLY_DATE
------- ----------
D02 430
Solutions
You can replace the keep Syntax by rewriting Sql statements.
Examples
canno=> select dept_id,min(sal_cnt) from (select dense_rank() over (partition by dept_id order by sale_date),* from criss_sales where dept_id = 'D02' ) t where dense_rank=1 group by dept_id;
dept_id | min
---------+-----
D02 | 430
(1 row)