INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS
Come Join Us!
Are you a
Computer / IT professional?
Join Tek-Tips now!
- 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.
Partner With Us!
"Best Of Breed" Forums Add Stickiness To Your Site
(Download This Button Today!)
Feedback
"...I have to add my thanks and appreciation for your wonderful site... People who frequent the site are the two best things - nice and smart..."
Geography
Where in the world do Tek-Tips members come from?
|
Microsoft: Visual FoxPro FAQ
|
SQL Syntax
|
How to remove duplicate entries and keep the highest price items
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
|
|
 |
|
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:
Talk To Other Members- Notification Of Responses To Questions
- Favorite Forums One Click Access
- Keyword Search Of All Posts, And More...
Register now while it's still free!
Already a member? Close this window and log in.
Join Us Close
