表 A CIFNO,OPENDATE
1 2012-2-16
1 2012-4-16
2 2012-5-16
2 2012-6-16
3 2012-9-16
3 2012-4-16
3 2012-3-16
怎么写取出结果为
表 A CIFNO,OPENDATE
1 2012-2-16
2 2012-5-16
3 2012-3-16
1 2012-2-16
1 2012-4-16
2 2012-5-16
2 2012-6-16
3 2012-9-16
3 2012-4-16
3 2012-3-16
怎么写取出结果为
表 A CIFNO,OPENDATE
1 2012-2-16
2 2012-5-16
3 2012-3-16
select * from (select c.cifno,c.opendate from A c group by c.cifno,c.opendate order by c.opendate ) x
where rownum=1;我写了不对。。所以来求教
CIFNO,
OPENDATE,
dense_rank() over(partition by CIFNO order by OPENDATE) rank
from (select c.cifno,c.opendate from A c group by c.cifno,c.opendate order by c.opendate ) x
where rank=1;
create table A (CIFNO number(10),OPENDATE date);insert into A values (1,date'2012-02-16');
insert into A values (1,date'2012-04-16');
insert into A values (2,date'2012-05-16');
insert into A values (2,date'2012-06-16');
insert into A values (3,date'2012-09-16');
insert into A values (3,date'2012-04-16');
insert into A values (3,date'2012-03-16');select CIFNO,OPENDATE from
(select CIFNO,OPENDATE,row_number() over(partition by CIFNO order by OPENDATE) rn
from A)
where rn=1 CIFNO OPENDATE
-------------------------------------
1 1 2012/2/16
2 2 2012/5/16
3 3 2012/3/16
select distinct CIFNO,OPENDATE
from A t1
where OPENDATE=(select min(OPENDATE) from A where a.cifno=t1.cifno)
order by CIFNO
CIFNO OPENDATE
-------------------------------------
1 1 2012/2/16
2 2 2012/5/16
3 3 2012/3/16
SELECT CIFNO,Min(OPENDATE) FROM A GROUP BY CIFNOCIFNO MIN(OPENDATE)
1 16.02.2012 00:00:00
2 16.05.2012 00:00:00
3 16.03.2012 00:00:00