关于两个重复字段的问题

[pyguang]

DSN NAME PID NI TIME
11 A1 113 74 20130730085133
11 B3 113 62 20140330095121
23 B4 4 5 7 20130720085123
92 B5 7 6 5 20130720015115
结果
DSN NAME PID NI TIME
11 A1 113 74 20130730085133
23 B4 4 5 7 20130720085123
92 B5 7 6 5 20130720015115

DSN,PID 重复只留一条，取TIME少的那条
语句怎么写

[Shawn]

SELECT * FROM TB A
WHERE NOT EXISTS
(
	SELECT 1
	FROM TB B
	WHERE b.DSN  = a.DSN
		AND b.PID = a.PID
		AND b.[TIME] < a.[TIME]
)

[Andy__Huang]

create table #tb(DSN varchar(2), NAME varchar(2), PID int,NI int,[TIME] varchar(20))
insert into #tb
select 11,'A1',113,74,'20130730085133'
union all select 11,'B3',113,62,'20140330095121'
union all select 23,'B4',45,7,'20130720085123'
union all select 92,'B5',76,5,'20130720015115'

select DSN,NAME,PID,NI,[TIME]
from (select *,ROW_NUMBER() over(partition by DSN order by PID ) rn  from #tb)t
where rn=1

/*
DSN	NAME	PID	NI	[TIME]
11	A1	113	74	20130730085133
23	B4	45	7	20130720085123
92	B5	76	5	20130720015115
*/

[Shawn]

--方法2
SELECT B.*
FROM (SELECT DISTINCT DSN, PID FROM tb) a 
CROSS APPLY
(SELECT TOP(1) * FROM TB m WHERE m.DSN=a.DSN AND m.PID=a.PID ORDER BY m.[TIME]) b

[Shawn]

--方法3
;WITH cte AS
(
	SELECT rowid =ROW_NUMBER() OVER(PARTITION BY DSN, PID ORDER BY [time]), *
	FROM TB
)
SELECT DSN,NAME,PID,NI,[TIME]
FROM cte
WHERE rowid = 1

[pyguang]

呵呵，你们很给力，谢谢了，送分