sql 去重复行问题

[lifang0803]

if not object_id('tb') is null
drop table tb
Go
Create table tb([pkid] int,[sid] int,[nid] int,[price] int)
Insert tb
select 12,123,124,100 union all
select 13,123,124,300 union all
select 14,133,134,100 union all
select 15,133,134,300 union all
select 16,164,165,200 union all
select 17,164,165,200 union all
select 18,174,1175,100 union all
select 21,173,174,100 union all
select 22,173,174,300 union all
select 31,183,184,100 union all
select 35,183,184,300
Go

要求：去除字段sid ,nid 重复的行。 并且取到的行的price 必须是最高的！ 比如以上数据我取到的应该是 ：
13,123,124,300
15,133,134,300
16,164,165,200
18,174,1175,100
22,173,174,300
35,183,184,300
谢谢！

[RUNBEAR]

DELETE tb 
WHERE EXISTS 
(
	SELECT * FROM tb tb2
	WHERE tb.sid=tb2.sid AND tb.nid=tb2.nid
	AND tb.price<=tb2.price
	AND tb.pkid<tb2.pkid
)

下次20分，很少人回答你的。

[挨踢直男]

楼主的头像是楼主人吗?

[挨踢直男]

select * from tb a where pkid = 
(
 select max(pkid) from tb where nid = a.nid and sid = a.sid and price = 
(
select max(price) from tb where nid = a.nid and sid = a.sid
)
)