SQL> create table test (idnumber(2) , code varchar2(10) , column_date varchar2(100));
Table created.
SQL>
SQL> insert into test values (1 ,'A', '200301');
1 row created.
SQL> insert into test values (2 ,'A', '200305');
1 row created.
SQL> insert into test values (3 ,'B', '200302');
1 row created.
SQL> insert into test values (4 ,'C', '200212');
1 row created.
SQL> insert into test values (5 ,'B', '200211');
1 row created.
SQL> insert into test values (6 ,'B', '200307');
1 row created.
SQL> insert into test values (7 ,'A', '200210');
1 row created.
SQL> insert into test values (8 ,'C', '200305');
1 row created.
SQL> commit;
Commit complete.
SQL> set linesize 1000
SQL> select * from test where (id , code , to_date(column_date,'yyyymm') ) in
2(select id , code, max( to_date(column_date,'yyyymm')) over (partition by code)maxdate
3from test )
4order by id ;
ID CODE COLUMN_DATE
---------- ---------- ------------------------------------------------------------------------------
2 A
200305
6 B
200307
8 C
200305
这可能不是最好的办法，因为它涉及子查询。我从直觉上以为可以使用分析函数，我再找找。楼上的两个帖子会有问题的。一个数据不对，一个会报语法错误。
|
