Duplicate Records in MSSQL

How to remove the unwanted records?

[Easy_Coder]

I know how to isolate the dupes but how do I remove the unwanted records and keep the one with the lowest discount?

This is what I have
ITEM DISCOUNT
ABC .20
ABC .25
ABC .35
DEF .15
DEF .35

This is where I want to get to
ITEM DISCOUNT
ABC .20
DEF .15

Can I pull this off with a set based operation or am I cursor bound?

[emsaw]

I found something like this on another site and used the methodology to detect and remove duplicates in one of my databases. Worked/works like a charm:

SELECT distinct a.item_id
FROM ItemDiscount a, ItemDiscount b
WHERE a.discount > b.discount
AND a.item_description = b.item_description
ORDER BY a.item_id

This would be a list of the duplicates only, so you can delete these.
If you play around with this method, you should be able to get what you want.
HTH,

Mark

[Easy_Coder]

>>you should be able to get what you want

works perfectly, thanks.