Duplicate records removal

[lyudmila]

I have table with fields as follow
PO_NBR Store ORD_DT RCV_DT PO_Cost
6 56 1/2/01 1/10/01 1500.00
6 70 2/1/02 2/7/02 null
112 87 12/3/00 12/8/00 1300.00
112 36 5/4/01 5/19/01 500.00
112 36 5/5/01 null null
132 87 6/7/01 6/10/01 125.00

No primary key, but field PO_NBR+ Store are used to link to PO Detail table.
I need identify duplicate records with duplicate key fields, in case of duplicate PO_NBR, remove records with ORD_DT=null and RCV_DT =null and PO_Cost=0, keep record with min(ORD_DT) an min(RCV_DT). What is the best way to appoch this problem? I work with client data, I get data as it gets, I do not need suggestion, how to improve data. Thank you.

 

[jimmYLepp]

I'm kinda confused are you looking for a way to delete the extra data in the table or are you looking for a better way to confirm the data is good before it gets into the table?

jimmY

 

[lyudmila]

Yes, I need to delete duplicated (key duplicated )unwanted records.

 

[tlbroadbent]

Here is one possible solution. You may need to modify it slightly to handle the data in your table.

Replace Delete Your table with Select tbl.* to list delete candidates. You will be able to verify that the query is selecting the correct rows from the table.

Delete YourTable
From YourTable tbl
Left Join
(Select PO_NBR, Store, ORD_DT=Min(ORD_DT)
From YourTable
Where Ord_DT Is Not Null And RCV_DT Is Not Null
Group By PO_NBR, Store) qry
On tbl.PO_NBR=qry.PO_NBR
And tbl.Store = qry.Store
And tbl.ORD_DT = qry.ORD_DT
Where qry.PO_NBR Is Null Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.