INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

SQL Syntax

How to remove duplicate entries and keep the highest price items by keepingbusy
Posted: 23 Jan 04

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.......

Back to Microsoft: Visual FoxPro FAQ Index
Back to Microsoft: Visual FoxPro Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close