How to: Remove highest price item when more than one found 4

[keepingbusy]

Hi all

Here's the scenario:

We have a table that used to have a couple of thousand records / titles but that has now increased to over 400,000. I am currently using the code below to search for any titles within the table that match by way of UPC number. This is a unique number that relates to the title of a CD / DVD etc for example: 012345678910 and is stored in character field. As there is usually more than one of the same UPC number (after merging three tables) we only need to remove the highest price item(s) and just keep the single lowest one, example:

Kelly Clarkson - Greatest Hits - £14.99 - 01234567
Kelly Clarkson - Greatest Hits - £13.99 - 01234567
Kelly Clarkson - Greatest Hits - £11.45 - 01234567

Needles to say we want to delete the top two and just keep the £11.45 one. This works with a few thousand records quite quickly but with 400,000, well....

tempfile=SYS(3)  && Create temp file
USE MAINCAT EXCLUSIVE
	
COPY STRU TO tempfile+'.DBF'
COPY STRU TO NEWFILE.DBF
	
CLOSE DATABASES

USE tempfile+'.DBF' EXCL
APPEND FROM MAINCAT
DELE FOR EMPTY(UPC) && Remove empty recs
PACK
GO TOP

A=1
DO WHILE NOT EOF()
  REPLACE URN WITH A
  A=A+1
  SKIP
ENDDO

GO TOP
	
mreccount=0
mreccount=RECCOUNT()

DO WHILE NOT EOF()
  mlowest=SYS(3)
  STORE SPACE(18) TO mupc
  STORE UPC TO mupc
		
  mthis=0
  mthis=RECNO()

  CLOSE DATABASES

* Select all records matching UPC number
* regardless of price

  SELECT * FROM tempfile+'.dbf' WHERE;
   UPC=mupc ORDER BY SELL INTO TABLE mlowest+'.DBF'
  GO TOP

  murn=0
  murn=URN
  DELE FOR URN<>murn
  PACK
		
  CLOSE DATABASES
  USE NEWFILE EXCL
  APPEND FROM mlowest+'.DBF'
		
  CLOSE DATABASES
  DELE FILE mlowest+'.dbf'
		
  USE tempfile+'.dbf' EXCL
  DELE FOR UPC=mupc
  GO mthis
  SKIP
ENDDO	

CLOSE DATABASES
	
USE tempfile+'.DBF' EXCL
PACK

CLOSE DATABASES

CLEAR
SET SAFETY OFF

CLOSE DATABASES

USE MAINCAT EXCL
ALTER TABLE MAINCAT DROP COLUMN URN
ZAP
APPEND FROM tempfile+'.dbf'
PACK

* .....Done etc

Can anyone please suggest an easier / quicker way to achieve a faster result?

Many thanks in anticipation

Lee


Visual FoxPro Versions: 6 & 9
Operating System: Windows XP

http://www.customsoftware.co.uk

 

[keepingbusy]

Thank you Mike

Lee

Visual FoxPro Versions: 6 & 9
Operating System: Windows XP

http://www.customsoftware.co.uk