Duplicate query with complex delete function

[rushforth]

I've have a duplicate query running on a table containing 1000s of records. I need to retain the highest batch number from the property reference group (the duplicate field). Basically keep the records in red in the example below...

Property Reference

Batch

1070

354

1070

491

1070

565

1070

581

1070

582

1074

491

1074

565

1074

581

1074

682

1075

353

1075

354

1075

491

Hope someone can help. This is my first post on the forum.

Many thanks in advance.

[TheShabz]

SELECT Reference, Batch
FROM myTable
GROUP BY max(Batch)

[rushforth]

Thanks. Now I've identified the 'max' in each group of duplicates how do I delete all but the max records?

[TheShabz]

instead of a select query, turn it into a make table. make a new table, delete the old one and rename this to the deleted one. Just make sure you dont need all that data first.

[rushforth]

Thanks again. Unfortunatly the master table, where the dup refs are, has more than the two columns I originally posted. How can I make a new table, with all the fields, with the totals column showing?

[TheShabz]

are all the fields besides the Batch exactly the same?

[rushforth]

No. So I cant set the total of all fields to 'Group By'.

[TheShabz]

On second thought, I honestly think you'd be better off leaving the data and adding a field to the table with an indicator instead of deleting the records. Add a field called "maxBatchInd" then run a query
UPDATE masterTable
SET maxBatchInd = "whatever you want here"
WHERE newTable.reference = masterTable.Reference AND newTable.batch = masterTable.Batch

This will put an indicator (whatever you set it to) in the maxBatchInd field. You can then query off of the indicator without losing all your data. If you really want to delete the other data, after setting the indicator, you can simply...
DELETE *
FROM masterTable
WHERE maxBatchInd Is Null

[rushforth]

Thanks TheShabz. I'll try this out when I get in to the office. I'll let you know.

[rushforth]

Thanks, worked a treat!