请教一条sql语句如何写。

[hc860102]

有表A，表A里数据如下
id date card accno ff_date
1 20110401 440301196804014128 019570200012501 2010-4-26
2 20110401 440301196804014128 019570200012401 2010-5-7
3 20110401 522601196610040828 159570200002001 2010-5-14
4 20110401 522601196610040828 159570200002001 2010-6-05
5 20110401 522601196610040828 159570200002001 2010-6-28
6 20110401 522601196610040828 159570200002001 2010-7-14
7 20110401 888888888888888888 555555555555555 2010-5-10

我要从表A里取出每个accno对应最小日期的一条记录，即我查询后应该得到如下结果：
id date card accno ff_date
1 20110401 440301196804014128 019570200012501 2010-4-26
3 20110401 522601196610040828 159570200002001 2010-5-14
7 20110401 888888888888888888 555555555555555 2010-5-10

请问SQL查询语句怎么写～～

[虫洞]

select t.*
from
(
select a.*
      ,row_number() over(partition by a.accno order by a.ff_date) rn
from a
)t
where rn=1
;

[BenChiM888]

[SYS@orcl] SQL>with t1 as(
  2  select 1 id, '20110401' date1, '440301196804014128' card, '019570200012501' acno,'2010-4-26' ff_Date from dual union all
  3  select 2 id, '20110401' date1, '440301196804014128' card, '019570200012401' acno,'2010-5-7'  ff_Date from dual union all
  4  select 3 id, '20110401' date1, '522601196610040828' card, '159570200002001' acno,'2010-5-14' ff_Date from dual union all
  5  select 4 id, '20110401' date1, '522601196610040828' card, '159570200002001' acno,'2010-6-05' ff_Date from dual union all
  6  select 5 id, '20110401' date1, '522601196610040828' card, '159570200002001' acno,'2010-6-28' ff_Date from dual union all
  7  select 6 id, '20110401' date1, '522601196610040828' card, '159570200002001' acno,'2010-7-14' ff_Date from dual union all
  8  select 7 id, '20110401' date1, '888888888888888888' card, '555555555555555' acno,'2010-5-10' ff_Date from dual
  9  )select min(id)keep(dense_rank first order by ff_Date) id,
 10          min(date1)keep(dense_rank first order by ff_Date) date1,
 11          card,
 12          min(acno)keep(dense_rank first order by ff_Date) acno,
 13          min(ff_Date)keep(dense_rank first order by ff_Date) id
 14   from t1
 15   group by card;

        ID DATE1    CARD               ACNO            ID
---------- -------- ------------------ --------------- ---------
         1 20110401 440301196804014128 019570200012501 2010-4-26
         3 20110401 522601196610040828 159570200002001 2010-5-14
         7 20110401 888888888888888888 555555555555555 2010-5-10

[BenChiM888]

[SYS@orcl] SQL>with a as(
  2  select 1 id, '20110401' date1, '440301196804014128' card, '019570200012501' acno,'2010-4-26' ff_Date from dual union all
  3  select 2 id, '20110401' date1, '440301196804014128' card, '019570200012401' acno,'2010-5-7'  ff_Date from dual union all
  4  select 3 id, '20110401' date1, '522601196610040828' card, '159570200002001' acno,'2010-5-14' ff_Date from dual union all
  5  select 4 id, '20110401' date1, '522601196610040828' card, '159570200002001' acno,'2010-6-05' ff_Date from dual union all
  6  select 5 id, '20110401' date1, '522601196610040828' card, '159570200002001' acno,'2010-6-28' ff_Date from dual union all
  7  select 6 id, '20110401' date1, '522601196610040828' card, '159570200002001' acno,'2010-7-14' ff_Date from dual union all
  8  select 7 id, '20110401' date1, '888888888888888888' card, '555555555555555' acno,'2010-5-10' ff_Date from dual
  9  )select t.*
 10  from
 11  (
 12  select a.*
 13        ,row_number() over(partition by a.card order by a.ff_date) rn
 14  from a
 15  )t
 16  where rn=1
 17  ;

        ID DATE1    CARD               ACNO            FF_DATE           RN
---------- -------- ------------------ --------------- --------- ----------
         1 20110401 440301196804014128 019570200012501 2010-4-26          1
         3 20110401 522601196610040828 159570200002001 2010-5-14          1
         7 20110401 888888888888888888 555555555555555 2010-5-10          1

[qyaohai]

select a.* from a join (select accno,min(ff_date) tt from a group by accno) b on a.accno=b.accno and a.ff_date=b.tt

[njlywy]

select t.* 
from
(select a.*,row_number() over(partition by a.card order by a.ff_date) 
rn from a)t 
where rn=1;

[不要悲剧人生]

同意楼上的。。

[dmuzxc]

受教了 学习了