求一删除语句

[arfeng75]

有下面的表TEST：
-------------------------
序号 工号
1 D01
2 D02
3 E07
4 E08
5 D02
6 E07

--------------------------

求一删除语句，可以删除上表中“工号”重复且“序号”大的记录，只保留“序号”最小的记录，比方说上表删除以后的结果是：

序号 工号
1 D01
2 D02
3 E07
4 E08

[arfeng75]

请注意是删除语句。谢谢

[CSDMN]

delete a
from test a
where exists (
select 1 from test
where 工号=a.工号
and 序号<a.序号
)

[CSDMN]

or:

delete a
from test a
where 序号<> (
select min(序号) from test
where 工号=a.工号
)

[chd2001]

delete from tb a
where xh in
(select xh from tb where not exists(a.xh<xh) group by gh having count(1)>1)

[dulei115]

delete from test
where 序号 not in (select min(序号) from test group by 工号)

[chd2001]

delete from tb a
where xh in
(select xh from tb where a.xh>xh group by gh having count(1)>1)

[marco08]

delete test where 序号 not in(
select min(序号) from test group by 工号
)

[mengwuxing]

=========================================================
delete from test
where 序号 not in (select min(序号) from test group by 工号)