求高手帮忙。看看这个sql语句有难度··

[likf521]

表名: TEST 字段: Id,A,B,C,D
其中B字段包含重复值;

Id A B C D
1 11 a 34 2010-08-18 20:01:19.810
2 22 a 35 2010-08-18 20:02:01.153
3 33 d ht 2010-08-18 20:03:53.577

D字段是时间字段，我要保留B字段重复数据中时间最前面的一条数据，其余B字段重复的都删除···
问下sql改怎么实现·
select B from TEST group by B having count(*) >1
这条是查询出来重复的字段的数据·
比如B字段中，ID为1，2的相同，我就保留 D字段的时间为2010-08-18 20:01:19.810
时间最前面的数据··也就ID为1的数据·
求大侠帮帮忙
Id A B C D
1 11 a 34 2010-08-18 20:01:19.810
3 33 d ht 2010-08-18 20:03:53.577
结果就这样·

[一切为了你]

select distinct B from TEST order by D asc

[likf521]

删除重复的数据，保留时间最小的，帮小弟一个忙啊·

[winner2050]

很简单。
Delete from test where id <>(select top 1 from test order by B desc)

[gcyabc123]

笨办法
DELETE FROM TEST TS
WHERE NOT EXISTS (SELECT 1
FROM (SELECT B, MIN(d) D FROM TEST GROUP BY B) A
WHERE TS.B = A.B
AND TS.D = A.D)

[wuyq11]

delete from TEST where exists(select 1 from TEST a where a.id>TEST.id and TEST.B=a.B)

[likf521]

DELETE FROM member WHERE memberid not in(SELECT memberid
FROM member as TS,(SELECT mobile, MIN(certdate) certdate FROM member group by mobile having count(*) >1 ) as A
WHERE TS.mobile = A.mobile
AND TS.certdate = A.certdate)
and mobile IN (select mobile from member group by mobile having count(*) >1 )

[likf521]

嘻嘻``OK了·谢谢楼上的各位仁兄啊··