Create a query to remove duplicate records by choosing the highest order num

[accessfever]

Hi, I have a big table in which same product numbers may have multiple records with different reference number(s) in the table (For example, if the part has n reference numbers that it has n records in the table). I want to keep only one record for one part in the table by choosing the hgihest order number I established in another table named 'COSTRUN_ORD' to provide the order number for those reference numbers. I want to keep only one record for each loc and proc with the highest number in the new table.
Below is my example:

The primay table'd data is something like this:
ZZZ TESTLOCPROCNUM1NUM2REFNOCHICAGOABC323425AJUN1CHICAGOABC323425AJUNKCHICAGOBCD323455AJUN1CHICAGOBCD323455AJUNRCHICAGOBCD323455AJUNKCHICAGODEF425678AJUN1

The control table's data is as follow:

COSTRUN_ORDCOSTING RUNORDERAJUN17AJUNR9AJUNK8

The desired result for new table would be like this after keeping the highest order for each loc and proc:

ZZZ TEST2LOCPROCNUM1NUM2REFNOCHICAGOABC323425AJUNKCHICAGOBCD323455AJUNRCHICAGODEF425678AJUN1

I tried to create a couple queries but didn't get the result like above.

Any idea would be appreciated.

 

[Guus2005]

First determine which records have the highest Id's:

select max(id) as MaxID from table1

those are the records you wish to keep.

Now create a delete query:

delete from table1 where id not in (select MaxID from query1)

Enjoy!

 

[accessfever]

I will give it a shot. Thanks for the tips.