关于删除重复记录

Question

执行以下SQL 脚本后，会生成表zzzz_Test_WWCREATE TABLE [zzzz_Test_WW] (
[CheckDate] [varchar] (10) NULL ,
[ref_category] [int] NULL ,
[RType] [varchar] (9) ,
[number] [int] NULL 
) 
go
INSERT INTO zzzz_Test_WW(CheckDate,ref_Category,RType,number)
values('2010-10-19','491','Reference','2999394')
go
INSERT INTO zzzz_Test_WW(CheckDate,ref_Category,RType,number)
values('2010-10-20','491','Reference','3004054')
go
INSERT INTO zzzz_Test_WW(CheckDate,ref_Category,RType,number)
values('2010-10-21','491','Reference','3004054')
go
INSERT INTO zzzz_Test_WW(CheckDate,ref_Category,RType,number)
values('2010-10-22','491','Reference','3004054')
go
INSERT INTO zzzz_Test_WW(CheckDate,ref_Category,RType,number)
values('2010-10-23','491','Reference','3004054')
go
INSERT INTO zzzz_Test_WW(CheckDate,ref_Category,RType,number)
values('2010-10-24','491','Reference','3004054')
go
INSERT INTO zzzz_Test_WW(CheckDate,ref_Category,RType,number)
values('2010-10-25','491','Reference','3004054')
go
INSERT INTO zzzz_Test_WW(CheckDate,ref_Category,RType,number)
values('2010-10-26','491','Reference','3004054')select * from zzzz_Test_WW 会得到以下结果CheckDate      Category      RType      number 2010-10-19 491 Reference   2999394
2010-10-20 491 Reference   3004054
2010-10-21 491 Reference  3004054
2010-10-22 491 Reference   3004054
2010-10-23 491 Reference   3004054
2010-10-24 491 Reference   3004054
2010-10-25 491 Reference   3004054
2010-10-26 491 Reference   3004054我想把number重复的记录删除掉，仅保留CheckDate最小的记录，即得到以下结果：CheckDate      Category      RType      number 2010-10-19 491 Reference   2999394
2010-10-20 491 Reference   3004054
请问，我该如何写语句
谢谢大家

Answer 1

delete t from zzzz_Test_WW t
where exists (select 1 from zzzz_Test_WW where number=t.number and CheckDate<t.CheckDate)select * from zzzz_Test_WW

Answer 2

select * from zzzz_Test z
where not exists(select null from zzzz_Test where z.Category =Category and CheckDate <t.CheckDate )

Answer 3

delete  from zzzz_Test_WW t
where CheckDate>(select min(CheckDate) from zzzz_Test_WW where number=t.number)