SQL> CREATE TABLE TestTb (a varchar2(21), b int ,c int);
Table created
SQL> insert into testtb values(13400000000,1,9);
1 row inserted
SQL> insert into testtb values(13400000001,1,9);
1 row inserted
SQL> insert into testtb values(13400000002,1,9);
1 row inserted
SQL> insert into testtb values(13400000003,1,9);
1 row inserted
SQL> insert into testtb values(13400000001,1,7);
1 row inserted
SQL> insert into testtb values(13400000001,2,8);
1 row inserted
SQL> insert into testtb values(13400000001,3,7);
1 row inserted
SQL> commit;
Commit complete
SQL> select distinct a,d from(
2select a,b,c,
3max(b) keep (DENSE_RANK first ORDER BY c) over(partition by a) d
4from testtb
5) order by a;
A
D
--------------------- ----------
13400000000
1
13400000001
3
13400000002
1
13400000003
1
SQL>
KEEP的详细用法参看下边帖子的84楼:[/COLOR]
http://www.itpub.net/showthread. ... 10&pagenumber=9
|