Deleting Duplicate Rows - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Deleting Duplicate Rows

Author

Topic

token
Posting Yak Master

133 Posts

Posted - 2009-07-27 : 18:24:07

Hello!

I would like to delete records from my table called "Products" where there are duplicates. But I only want to delete duplicates which have a higher price than the cheapest one. The table has four columns:

ProductID
CostPrice
Manufacturer
PartNo

The fields "Manufacturer" and "PartNo" together identify each item.

In order to get the lowest CostPrice for each unique combination of Manufacturer and PartNo I use the following query:

(SELECT min(CostPrice),
Manufacturer, PartNo
FROM Products
GROUP BY Manufacturer, PartNo)

How would I delete the other records in the Products table that didn't get returned from the above query?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-27 : 19:22:13

DELETE f
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY Manufacturer, PartNo ORDER BY CostPrice DESC) AS recID
FROM Products
) AS f
WHERE recID > 1

N 56°04'39.26"
E 12°55'05.63"

token
Posting Yak Master

133 Posts

Posted - 2009-07-27 : 20:13:08

Hi

Thank you for the reply. The solution doesn't seem to work. It left me with rows that had the highest value in the CostPrice field, not the other way round!

I tried changing the query from DESC to ASC but nothing seems to have helped.

Any other ideas?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-27 : 20:20:27

changing to ORDER BY CostPrice ASC should be the one you want. If not, post sample data and show the required result

KH
[spoiler]Time is always against us[/spoiler]

token
Posting Yak Master

133 Posts

Posted - 2009-07-27 : 20:53:29

Okay,

If I run the following query:

SELECT CostPrice,
Manufacturer, PartNo
FROM Products

I get this result:
479.00 Sony KDL32V5500
519.80 Sony KDL32V5500

There are duplicate rows for the Manufacturer and PartNo, but one is cheaper than the other. So I want to get rid of the expensive one.

If I run the following query:
SELECT min(CostPrice),
Manufacturer, PartNo
FROM Products
GROUP BY Manufacturer, PartNo

I get the correct result:
479.00 Sony KDL32V5500

But even though that is the correct result, the other duplicate row still exists in the table. I want to delete it completely. How can I do this?

Using the query that Peso kindly gave, it seemed to have deleted the cheaper one and left the expensive one. If I change the ORDER BY to ASC, it left both the expensive and cheap one i.e nothing changed.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-27 : 21:10:11

[code]
DECLARE @sample TABLE
(
CostPrice decimal(10,2),
Manufacturer varchar(5),
PartNo varchar(10)
)
INSERT INTO @sample
SELECT 479.00, 'Sony', 'KDL32V5500' UNION ALL
SELECT 519.80, 'Sony', 'KDL32V5500'

SELECT *
FROM @sample

/*
CostPrice Manufacturer PartNo
------------ ------------ ----------
479.00 Sony KDL32V5500
519.80 Sony KDL32V5500

(2 row(s) affected)
*/

DELETE f
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY Manufacturer, PartNo ORDER BY CostPrice ASC) AS recID
FROM @sample
) AS f
WHERE recID > 1

SELECT *
FROM @sample

/*
CostPrice Manufacturer PartNo
------------ ------------ ----------
479.00 Sony KDL32V5500

(1 row(s) affected)
*/
[/code]

KH
[spoiler]Time is always against us[/spoiler]

token
Posting Yak Master

133 Posts

Posted - 2009-07-28 : 06:26:55

Yes it works! I think I corrupted my data. Once I dropped the table and repopulated it, the query worked a treat. I have never seen this "PARTITION" feature before - I should probably get a new book on SQL.

Finally, I can't get my head around how to compare just the first 7 characters of PartNo with other PartNos in the table. Because sometimes people write the same PartNo in different ways.

For example, person 1 writes:
£479.00 Sony KDL32V55/00U

Person 2 writes:
£518.00 Sony KDL32V5500U

If I use the code you guys kindly provided, it keeps both rows. Ideally I would like it to compare the first 7 characters of PartNo rather than the whole PartNo. Is this possible?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-28 : 07:04:58

[code]
ROW_NUMBER() OVER (PARTITION BY Manufacturer, LEFT(PartNo, 7) ORDER BY CostPrice ASC) AS recID
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources