求一条sql语句，

[rottenapple]

deal表中存有如下记录:
clientID, ClientName, Log_Date
001, s1,2009-01-01
002,s2,2009-02-01
001,ss1,2009-04-01

理论上来说，所有clientID是001的名字都应该相同，但是因为历史人工输入问题，可能造成不一致，我想取出唯一的id,name信息，如果id相同名字不同，则用最近的log_date的名字。怎么写这条语句？谢谢

[hebo2005]

select *
from 
(
select a.*,row_number() over(partition by clientid order by log_date desc) rn
from deal a
)
where 
rn=1

[robin_ares]

select distinct clientID,first_value(ClientName) over(partition by clientID order by Log_Date desc)
from a

[robin_ares]

select clientID,max(ClientName) keep(dense_rank first order by Log_Date desc)
from a
group by clientID

[welyngj]

select clientid,clientname,log_date
from deal d
where exists( select * from (select clientid,max(log_date) log_date from deal group by clientid) t where d.clientid=t.clientid
and d.log_date=t.log_date)

[阿三]

select * from
(
select clientid,clientname,log_date,row_number() over(partition by clientid order by log_date desc) rn from deal)
where rn=1

[bw555]

up，正解
[Quote=引用 1 楼 hebo2005 的回复:]
SQL codeselect *
from
(
select a.*,row_number() over(partition by clientid order by log_date desc) rn
from deal a
)
where
rn=1
[/Quote]

[inthirties]

[Quote=引用 1 楼 hebo2005 的回复:]
SQL code
select *
from
(
select a.*,row_number() over(partition by clientid order by log_date desc) rn
from deal a
)
where
rn=1


[/Quote]

select a.clientID, b. ClientName from deal a, (select Log_Date, clientID, ClientName, row_number() over(partition by clientid order by log_date desc) rn
from deal ) b where a. clientID = b. clientID

[oraclelogan]

[Quote=引用楼主 rottenapple 的帖子:]
deal表中存有如下记录:
clientID, ClientName, Log_Date
001, s1,2009-01-01
002,s2,2009-02-01
001,ss1,2009-04-01

理论上来说，所有clientID是001的名字都应该相同，但是因为历史人工输入问题，可能造成不一致，我想取出唯一的id,name信息，如果id相同名字不同，则用最近的log_date的名字。怎么写这条语句？谢谢
[/Quote]

select d2.clientID,d2.ClientName
from(
    select clientID,
           max(Log_Date) mld
      from deal group by clientID 
)d1, deal d2 where d1.clientID=d2.clientID and d1.mld=d2.Log_Date

[inthirties]

[Quote=引用 8 楼 oraclelogan 的回复:]
引用楼主 rottenapple 的帖子:

select d2.clientID,d2.ClientName
from(
select clientID,
max(Log_Date) mld
from deal group by clientID
)d1, deal d2 where d1.clientID=d2.clientID and d1.mld=d2.Log_Date
[/Quote]

最后的d1.mld=d2.Log_Date可以去掉吧

select d2.clientID,d2.ClientName
from(
select clientID,
max(Log_Date) mld
from deal group by clientID
)d1, deal d2 where d1.clientID=d2.clientID

[oraclelogan]

[Quote=引用 9 楼 inthirties 的回复:]
引用 8 楼 oraclelogan 的回复:
引用楼主 rottenapple 的帖子:

select d2.clientID,d2.ClientName
from(
select clientID,
max(Log_Date) mld
from deal group by clientID
)d1, deal d2 where d1.clientID=d2.clientID and d1.mld=d2.Log_Date



最后的d1.mld=d2.Log_Date可以去掉吧

select d2.clientID,d2.ClientName
from(
select clientID,
max(Log_Date)…
[/Quote]

不能去过，去了就有了一条重复记录了。

[suncrafted]

来晚了
帮顶

[outou]

[Quote=引用 1 楼 hebo2005 的回复:]
SQL codeselect *
from
(
select a.*,row_number() over(partition by clientid order by log_date desc) rn
from deal a
)
where
rn=1
[/Quote]
支持

[tiantom]

create table deal(clientID int,ClientName varchar2(20),log_date date);
insert into deal values(001,'s1',to_date('2009-1-1','yyyy-mm-dd'));
insert into deal values(002,'s2',to_date('2009-4-1','yyyy-mm-dd'));
insert into deal values(001,'ss1',to_date('2009-4-1','yyyy-mm-dd'));
commit;
select * from deal;

select clientid, clientname, log_date
from (select t.clientid,
t.clientname,
t.log_date,
row_number() over(partition by t.clientid order by t.log_date desc) rn
from deal t)
where rn = 1;