Remove duplicate items form table

Question

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26954 More actions · Answer 1

DECLARE @T TABLE(item INT,pricecode INT,price INT)

INSERT INTO @T(item,pricecode,price)

SELECT 1018,1,100 UNION ALL

SELECT 1020,2,120 UNION ALL

SELECT 1031,1,90 UNION ALL

SELECT 1045,5,45 UNION ALL

SELECT 1018,2,120 UNION ALL

SELECT 1430,7,35 UNION ALL

SELECT 1020,3,95 UNION ALL

SELECT 1430,5,30;

WITH CTE AS (

SELECT *,

ROW_NUMBER() OVER(PARTITION BY item ORDER BY price) AS rn

FROM @T)

DELETE FROM CTE

WHERE rn>1;

SELECT * FROM @T ORDER BY item

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

@DJ SSCommitted Points: 1989 More actions · Answer 2

Thanks Mark.

That was just brilliant.

Short end efective.

Dan

Mark Cowne One Orange Chip Points: 26954 More actions · Answer 3

Dan-Ketil Jakobsen (3/25/2011)
Thanks Mark.
That was just brilliant.
Short end efective.
Dan

Thanks for the feedback!

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum