create table t(
s1 varchar2(8),
s2 varchar2(8),
sfhm varhcar2(3)
);

数据如下：

'20011109'        '20020311'     '111'
'20030214'            ------           '111'
   -----                 '20110415'      '111'
'20011109'         '20020311'     '112'
'20030214'            -------           '112'
     ----                '20110415'     '112
要求如下：
在S1、S2存放的为日期值，SFHM放身份号码值。
现在想根据日期查找同一个身份号码的最新状态：取同一个身份号码中S1、S2中最大的日期值对应的那一条记录。

如 111 的最新记录是
      ---        '20110415'     '111'
============================================================

 select * from t;

S1       S2       SFHM
-------- -------- ----
20011109 20020311 111
20030214          111
         20110415 111
20011109 20020311 112
20030214          112
         20110415 112
20011109 20020311 444
20030214          444
20111111          444

9 rows selected

SQL>
SQL> select max(s1) keep(dense_rank last order by nvl(s1, s2)) s1,
  2         max(s2) keep(dense_rank last order by nvl(s1, s2)) s2,
  3         sfhm
  4    from t
  5   group by sfhm;

S1       S2       SFHM
-------- -------- ----
         20110415 111
         20110415 112
20111111          444

===================================================================

with a as(
select a.sfhm,max(a.s1) ma from t a group by a.sfhm
union all
select a.sfhm,max(a.s2) from t a group by a.sfhm)
select sfhm,max(ma) mm from a group by sfhm
1 444 20111111
2 112 20110415
3 111 20110415

===================================================================
 create table t as
 select 1 class1,2005-8-8 calldate,40 callcount from dual union all
 select 1 class1,2005-8-7 calldate,6 callcount from dual union all
 select 2 class1,2005-8-8 calldate,77 callcount from dual union all
 select 3 class1,2005-8-9 calldate,33 callcount from dual union all
 select 3 class1,2005-8-8 calldate,9 callcount from dual union all
 select 3 class1,2005-8-7 calldate,21 callcount from dual
 

 select class1,calldate,callcount,
 min(callcount) keep(dense_rank first order by class1 ) over(partition by calldate) min_first,
 max(callcount) keep(dense_rank first order by class1 ) over(partition by class1) max_first,
 min(callcount) keep(dense_rank first order by class1 desc) over(partition by calldate) dmin_first,
 max(callcount) keep(dense_rank first order by class1 ) over(partition by calldate) dmax_first,
 min(callcount) keep(dense_rank last order by class1 ) over(partition by calldate) min_first,
 max(callcount) keep(dense_rank last order by class1 ) over(partition by calldate) min_first,
 min(callcount) keep(dense_rank last order by class1 desc ) over(partition by calldate) min_first,
 max(callcount) keep(dense_rank last order by class1 desc) over(partition by calldate) min_first
 from t

    CLASS1   CALLDATE CALLCOUNT MIN_FIRST MAX_FIRST DMIN_FIRST DMAX_FIRST MIN_FIRST MIN_FIRST MIN_FIRST MIN_FIRST
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         3       1988         33         33         33         33         33         33         33         33         33
         1       1989         40         40         40          9         40          9          9         40         40
         2       1989         77         40         77          9         40          9          9         40         40
         3       1989          9         40         33          9         40          9          9         40         40
         1       1990          6          6         40         21          6         21         21          6          6
         3       1990         21          6         33         21          6         21         21          6          6

===============================================================================================

ORACLE中的KEEP()使用方法

2种取值：
DENSE_RANK FIRST
DENSE_RANK LAST

SQL> select * from test;

ID MC SL
-------------------- -------------------- -------------------
1 111 1
1 222 1
1 333 2
1 555 3
1 666 3
2 111 1
2 222 1
2 333 2
2 555 2

9 rows selected

SQL>
SQL> select id,mc,sl,
2 min(mc) keep (DENSE_RANK first ORDER BY sl) over(partition by id),
3 max(mc) keep (DENSE_RANK last ORDER BY sl) over(partition by id)
4 from test
5 ;

ID MC SL MIN(MC)KEEP(DENSE_RANKFIRSTORD MAX(MC)KEEP(DENSE_RANKLASTORDE
-------------------- -------------------- ------------------- ------------------------------ ------------------------------
1 111 1 111 666
1 222 1 111 666
1 333 2 111 666
1 555 3 111 666
1 666 3 111 666
2 111 1 111 555
2 222 1 111 555
2 333 2 111 555
2 555 2 111 555

9 rows selected

SQL>

不要混淆keep内（first、last）外（min、max或者其他）：
min是可以对应last的
max是可以对应first的
SQL> select id,mc,sl,
2 min(mc) keep (DENSE_RANK first ORDER BY sl) over(partition by id),
3 max(mc) keep (DENSE_RANK first ORDER BY sl) over(partition by id),
4 min(mc) keep (DENSE_RANK last ORDER BY sl) over(partition by id),
5 max(mc) keep (DENSE_RANK last ORDER BY sl) over(partition by id)
6 from test
7 ;

ID MC SL MIN(MC)KEEP(DENSE_RANKFIRSTORD MAX(MC)KEEP(DENSE_RANKFIRSTORD MIN(MC)KEEP(DENSE_RANKLASTORDE MAX(MC)KEEP(DENSE_RANKLASTORDE
-------------------- -------------------- ------------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
1 111 1 111 222 555 666
1 222 1 111 222 555 666
1 333 2 111 222 555 666
1 555 3 111 222 555 666
1 666 3 111 222 555 666

2 111 1 111 222 333 555
2 222 1 111 222 333 555
2 333 2 111 222 333 555
2 555 2 111 222 333 555

对于id=1的结果集进行一下解释
min(mc) keep (DENSE_RANK first ORDER BY sl) over(partition by id)：id等于1的数量最小的（DENSE_RANK first ）为
1 111 1
1 222 1
在这个结果中取min(mc) 就是111
max(mc) keep (DENSE_RANK first ORDER BY sl) over(partition by id)
取max(mc) 就是222；
min(mc) keep (DENSE_RANK last ORDER BY sl) over(partition by id)：id等于1的数量最大的（DENSE_RANK first ）为
1 555 3
1 666 3

在这个结果中取min(mc) 就是555，取max(mc)就是666

id=2的结果集同理