求sql server 查询语句

[guoheng90]

一个帖子回复表
replyId（回复编号）
topicId （主帖编号）
uerId（用户编号）
replyContent（回复内容）
replyTime（回复时间）

现在要获取回复帖子列表， 对于对同一个主帖回复的记录，只显示一条回复记录，且是时间最后的。
求sql 查询语句


数据如：
replyId topicId uerId replyContent replyTime
11 65 270412 sdf aw fdas 2010-03-08 16:06:07.373
12 96 36767 sdsdfsdfsadfsd 2010-03-09 14:15:39.267
13 96 36767 sadfsdfsadfd 2010-03-09 14:15:45.327
14 96 36767 sdfsadsadf 2010-03-09 14:15:47.953
20 96 36767 sfsafsdfsdfsa 2010-03-09 14:16:09.360
23 98 36767 sdfsdfsadfasdf 2010-03-10 09:21:34.860



想要得到的结果为：
replyId topicId uerId replyContent replyTime
11 65 270412 sdf aw fdas 2010-03-08 16:06:07.373
23 98 36767 sdfsdfsadfasdf 2010-03-10 09:21:34.860
23 98 36767 sdfsdfsadfasdf 2010-03-10 09:21:34.860

[黄_瓜]

select  * from tb a where not exists (select 1 from tb where replyId=a.replyId and
replyTime>a.replyTime)

[--小F--]

select
 *
from
 tb t
where
 replyTime=(select max(replyTime) from tb where topicId=t.topicId)

[黄_瓜]

select  * from tb a where replyTime=(select max(replyTime) from tb where replyId=a.replyId)

[喜-喜]

--------------------SQL Server数据格式化工具-------------------
---------------------------------------------------------------
-- DESIGNER :happycell188(喜喜)
--       QQ :584738179
-- Development Tool :Microsoft Visual C++ 6.0    C Language 
-- FUNCTION :CONVERT DATA TO T-SQL
---------------------------------------------------------------
-- Microsoft SQL Server  2005
-- Developer Edition on Microsoft Windows XP [版本 5.1.2600]
---------------------------------------------------------------
---------------------------------------------------------------

use test
go
if object_id('test.dbo.tb') is not null drop table tb
-- 创建数据表
create table tb
(
replyId int,
topicId int,
uerId int,
replyContent char(20),
replyTime datetime
)
go
--插入测试数据
insert into tb select 11,65,270412,'sdfawfdas','2010-03-08 16:06:07.373'
union all select 12,96,36767,'sdsdfsdfsadfsd','2010-03-09 14:15:39.267'
union all select 13,96,36767,'sadfsdfsadfd','2010-03-09 14:15:45.327'
union all select 14,96,36767,'sdfsadsadf','2010-03-09 14:15:47.953'
union all select 20,96,36767,'sfsafsdfsdfsa','2010-03-09 14:16:09.360'
union all select 23,98,36767,'sdfsdfsadfasdf','2010-03-10 09:21:34.860'
go
--代码实现
select replyId,topicId,uerId,replyContent,replyTime from
(select id=row_number()over(partition by topicId order by replyTime desc),* from tb)t
where id=1


/*测试结果

replyId topicId uerId replyContent replyTime
-------------------------------------------------------------------
11	65	270412	sdfawfdas           	2010-03-08 16:06:07.373
20	96	36767	sfsafsdfsdfsa       	2010-03-09 14:16:09.360
23	98	36767	sdfsdfsadfasdf      	2010-03-10 09:21:34.860

(3 行受影响)
*/

[envykok]

IF OBJECT_ID('TEMP') IS NOT NULL DROP TABLE TEMP

CREATE TABLE TEMP
(
	replyId INT,
	topicId INT,
	uerId INT,
	replyContent VARCHAR(100),
	replyTime DateTime
)

INSERT INTO TEMP
SELECT 11,65,270412,'sdf aw fdas','2010-03-08 16:06:07.373'
UNION ALL
SELECT 12,96,36767,'sdsdfsdfsadfsd','2010-03-09 14:15:39.267'
UNION ALL
SELECT 13,96,36767,'sadfsdfsadfd','2010-03-09 14:15:45.327'
UNION ALL
SELECT 14,96,36767,'sdfsadsadf','2010-03-09 14:15:47.953'
UNION ALL
SELECT 20,96,36767,'sfsafsdfsdfsa','2010-03-09 14:16:09.360'
UNION ALL
SELECT 23,98,36767,'dfsdfsadfasdf','2010-03-10 09:21:34.860'

SELECT replyId,topicId,uerId,replyContent,replyTime
FROM
(
SELECT	RANK() over(partition by topicId order by replyTime desc) AS ID,*
FROM	TEMP
) t
WHERE	ID=1