两张表连查取出不重复的信息

[CN_Azure]

Ask表
AskID Question
1 问题1
2 问题2

Reply表
ID Reply ReplyTime AskID
1 问题1 回答内容 2011/07/21 18:20:00 1
1 问题1 回答内容 2011/07/21 19:20:00 1
1 问题2 回答内容 2011/07/21 20:20:00 2

现在想要去的内容是
AskID Question Reply ReplyTime
1 问题1 问题1 回答内容 2011/07/21 19:20:00
2 问题2 问题2 回答内容 2011/07/21 20:20:00

Reply表中的信息有针对Ask表中问题1两次重复，想要结果中只取回答时间最晚的一条信息。

[CN_Azure]

谢谢大家，结贴了

[快溜]

select * from Ask a,Reply b 
where a.AskID=b.AskID 
and not exists(select 1 from Reply where AskID=b.AskID and ReplyTime>a.ReplyTime)

[cd731107]

select *
from ask a inner join Reply b on a.id=b.id
and not exists 
(select 1 from Reply c where c.Reply=b.Reply and c.ReplyTime>b.ReplyTime)

[cd731107]

select AskID,Question,Reply,ReplyTime
from ask a inner join Reply b on a.AskID=b.id
and not exists 
(select 1 from Reply c where c.Reply=b.Reply and c.ReplyTime>b.ReplyTime)

[--小F--]

select
 * 
from
 Ask a,Reply b 
where
 a.AskID=b.AskID 
and
 ReplyTime=(select max(ReplyTime) from Reply where AskID=b.AskID )

[liang145]

create table #Ask表
(AskID int, Question nvarchar(50))
insert #Ask表
select 1,'问题1' union all
select 2,'问题2'

create table #Reply表
(ID int,Reply nvarchar(100), ReplyTime datetime, AskID int)
insert #Reply表
select 1,'问题1 回答内容', '2011/07/21 18:20:00', 1 union all
select 1,'问题1 回答内容', '2011/07/21 19:20:00', 1 union all
select 1,'问题2 回答内容', '2011/07/21 20:20:00', 2

;with T as (select Row_number()over(partition by AskID order by ReplyTime desc) as num,
            * from #Reply表)
select a.AskID,a.Question,r.Reply,r.ReplyTime from #Ask表 as a 
join T as r on a.AskID=r.AskID where num=1

--AskID       Question   Reply          ReplyTime
------------- --------- --------------- -------------------------
--1           问题1     问题1 回答内容  2011-07-21 19:20:00.000
--2           问题2     问题2 回答内容  2011-07-21 20:20:00.000
--
--(2 row(s) affected)