select max( decode (ZXRQ, null, sysdate + 1, ZXRQ ) ) result , KHSH from table1 group by KHSH order by result--test18:12:01 SQL> select * from table1; KHSH ZXRQ
---------- ---------
1
1 16-MAY-05
2 11-MAY-05
2 15-MAY-05
3
36 rows selected.Elapsed: 00:00:00.01
18:12:17 SQL> select max( decode (ZXRQ, null, sysdate + 1, ZXRQ ) ) result , KHSH from table1 group
by KHSH order by result;RESULT KHSH
--------- ----------
15-MAY-05 2
19-MAY-05 1
19-MAY-05 3Elapsed: 00:00:00.01
---------- ---------
1
1 16-MAY-05
2 11-MAY-05
2 15-MAY-05
3
36 rows selected.Elapsed: 00:00:00.01
18:12:17 SQL> select max( decode (ZXRQ, null, sysdate + 1, ZXRQ ) ) result , KHSH from table1 group
by KHSH order by result;RESULT KHSH
--------- ----------
15-MAY-05 2
19-MAY-05 1
19-MAY-05 3Elapsed: 00:00:00.01
18:14:16 2 by KHSH;RESULT KHSH
--------- ----------
19-MAY-05 1
15-MAY-05 2
19-MAY-05 3Elapsed: 00:00:00.00
(SELECT KHSH,ZXRQ, ROW_NUMBER() OVER (PARTITION BY KHSH ORDER BY ZXRQ DESC NULLS FIRST) ROW_ID
FROM TABLE1)
WHERE ROW_ID = 1结果:
KHSH ZXRQ
1
2 2005-05-15
3