delete duplicate based on two distinct fields

[naitx]

Hi,

I have a table called Products with fields ProductID,ProductName,ProductLevel,Description.


ProductName ProductID ProductLevel Description
ProdA A1 level1 blah...
ProdA A1 level1 blah...
ProdC A2 level2 blah..
ProdD A3 level3 blah..
ProdD A3 level3 blah...
ProdE A4 level3 blah...
ProdE A4 level2 blah...

what I want is to delete duplicate records based on distinct ProductID and ProductLevel fields,the result will be something like this..

ProductName ProductID ProductLevel Description
ProdA A1 level1 blah...
ProdC A2 level2 blah..
ProdD A3 level3 blah..
ProdE A4 level3 blah...
ProdE A4 level2 blah...

btw,no PK

Thanks in Advance

 

[r937]

SELECT ProductName
, ProductID
, MAX(ProductLevel) AS ProductLevel
, MAX(Description) AS Description
INTO UniqueProducts
FROM Products
GROUP
BY ProductName
, ProductID

once this has run, drop Products, then rename UniqueProducts to Products


r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[guitarzan]

See faq701-5721

 

[naitx]

maybe I should post some real data from my database...

ProductName ProductID ProductLevel Description
ProdA 1-B68HT infant some description
ProdA 1-B68HT adult some description
ProdA 1-B68TF adult some description
ProdG 1-DE34G infant some description
ProdG 1-DE34G infant some description
ProdA 1-DE34G male some description
ProdF 1-DE34G male some description

result should be like this...

ProductName ProductID ProductLevel Description
ProdA 1-B68HT infant some description
ProdA 1-B68HT adult some description
ProdA 1-B68TF adult some description
ProdG 1-DE34G infant some description
ProdA 1-DE34G male some description

 

[naitx]

thanks for replies....I got it working!