How to remove duplicate entries and keep the highest price items

This FAQ was generated as a result of Thread184-746387 and with the kind help of the contributors involved

The problem originated from merging three tables which ended up with over 700000 records. Once the merge process was completed we then needed to remove all duplicate records but keep the highest priced item for example:

Greatest Hits of The 90's : CD: ú12.95
Greatest Hits of The 90's : CD: ú13.95
Greatest Hits of The 90's : CD: ú11.95
Greatest Hits of The 90's : DVD: ú11.95

The item priced at ú13.95 was kept and the other two removed although the DVD item was kept even though the title was the same.

Here's the code:

SET SAFETY OFF
SET EXCL OFF
CLOSE DATABASES
CLEAR ALL

IF FILE("NEWFILE.CDX")
DELE FILE("NEWFILE.CDX")
ENDI

tempfile=SYS(3) && Create Unique DBF file name

USE NEWFILE
COPY STRU TO tempfile+'.dbf'
USE

SELECT TITLE, max(NEWPRICE) as price FROM NEWFILE GROUP BY TITLE INTO TABLE tempfile+'.dbf'
CLOSE DATABASES

USE NEWFILE EXCL
ZAP
APPEND FROM tempfile+'.dbf'
INDEX ON TITLE TAG TITLE
CLOSE DATABASES
DELE FILE tempfile+'.dbf' && Delete the temp file
CLEAR
RETURN

Hope this helps anyone as much as it as helped us

Many thanks again to the very helpful assistance of the Tek-Tips forum members

Lee.......