Show only one record in duplicate search 1

[MikeMV]

Hello,

I am using VFP 9 to come up with a list of duplicate records in a remote view, I have this working, but I end up with a list that shows a full list of duplicates, I would like to weed this out so that it only shows me one entry per duplicate record and I am stuck trying to get this done without running too much code. Could you offer some insight as to whether this can be done within the commands to extract the data?

The way I have it setup now I end up with data that looks like this:

23308-BS/AZT 0
23308-BS/AZT 0

23308-BS/ICA 0
23308-BS/ICA 0

I will greatly appreciate your response.

*
OPEN DATABASE vpricelist
SET DATABASE TO vpricelist
*
SELECT * FROM vupload ;
WHERE model IN ;
(SELECT model ;
FROM vupload ;
GROUP BY model ;
HAVING Count(model)>1) ;
INTO ARRAY Duplicates

IF _TALLY>0
*
SELECT vupload

SELECT 2

USE chk_dup INDEX chk_dup
DELETE ALL
PACK
REINDEX
APPEND FROM ARRAY Duplicates FOR ASCAN(Duplicates,model) >0
*
EXPORT TO p:\Projects\intranet\chk_dup TYPE XL5
*
ENDIF
*
CLOSE DATABASES ALL
*
RETURN
*

 

[bborissov]

OK, but what you define as DUPLICATE?
Only Model?
What if you have different values in OTHER fields?
Something like:
[tt]
Model OtherField
-------------------
1 1
1 2
[/tt]
Is this duplicate entry or not?
And if this is am duplicate entry what record you want to export?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[MikeMV]

Thanks Borislav,

The duplicates would be based on the model field only, thus in your example they would be considered duplicates. We only need one entry per model in the table.

 

[bborissov]

Which one?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[bborissov]

And to make things difficult
[tt]
Model OtherField1 OtherField2
-------------------------------
1 1 2
1 2 1
[/tt]

Would you agree to get this result at the end:
[tt]
Model OtherField1 OtherField2
-------------------------------
1 2 2
[/tt]



Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[JRB-Bldr]

To continue Borislav's questions...

Or is the last record among the duplicates the most recent/most accurate record since it would typically represent the last entry made.

Good Luck,
JRB-Bldr

 

[MikeMV]

I am running VFP 9.0 SP2 and the backend is MS SQL 2005.

To us it does not matter which record is more recent or their content, what we are looking for is to only have a single record per model number.

What happens is that regardless of how they go in the table, all the duplicate records would be identical because they get populated from another table. We are trying to get the company that populates the table to figure out why sometimes the records go in duplicated, but in the meantime I have to figure out which ones are duplicated so that I can run a routine to delete them.

The results I am getting with the small routine I setup give me the duplicates, but I would like to see a single entry rather than all entries.

I hope I am not confusing things more.

Thanks.

 

[JRB-Bldr]

Do you want to merely "so that it only shows me one entry per duplicate record"?

Or do you want to eliminate the duplicate records and retain only a single record?

Good Luck,
JRB-Bldr

 

[bborissov]

mcampos,
You need to understand this:
There are NO dulicated records if some other fields are involved and the values of these fields differs.
So you must define WHAT make your record duplicate.
I only model, then you could use MAX(), MIN() to other fields to get only one record. But you could end up with my second example as a result (combination of several records into one).
That is why I asked you Which Record you want?
Did you have any Primary key in that table?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[MikeMV]

Thanks Borislav,

All duplicate records will always be identical, they come from the same source, they are copied from the source and never edited, but if they get copied twice they end up on the destination table more than once.

There is no primary key in the table and I have no control of that as this is table resides remotely and I have no say in how it is designed.

JRB- I am not trying to delete them just yet, I want to end up with the chk_dup local table containing a single copy of the duplicated records.


Thanks.

 

[JRB-Bldr]

I am certain that others out there may very likely have a number of more 'elegant' solutions, but here is a 'quick-and-dirty' method.

USE vupload IN 0
* --- Get All Records, But Add A Differentiating Field - RecNo ---
SELECT RECNO() as recno, ;
   vupload.* ;
   FROM vupload ORDER BY model;
   INTO TABLE C:\Temp\Temp

* --- Delete All Records But First One For Each Model ---
SELECT Temp
DELETE ALL
INDEX Model UNIQUE TAG Uniq
RECALL ALL
SET ORDER TO

* --- Only 'See' Non-Deleted Records ---
SET FILTER !DELETED()
<-- Do Whatever -->

By having the record number in the results, you can then, when you so desire, use that to delete all records for a given model which has a different record number.

Good Luck,
JRB-Bldr

 

[dgrewe]

Try this code if it is not more then you wanted.
Needless to say backup your table before running it, I did not test it.

*
* Save your delete setting
lcSetDelete = SET("DELETED")
SET DELETED OFF
*
* open your table and get rid of deleted record
OPEN DATABASE vpricelist
SET DATABASE TO vpricelist
SELECT 0
USE vupload EXCLUSIVE
PACK
*
*
DELETE ALL
INDEX ON MODEL+MAKE+ANYOTHERFIELD tag UNIQUE UNIQUE
SET ORDER TO UNIQUE
RECALL ALL 
*
* All duplicates are not marked as deleted
SET ORDER TO
COPY FOR !DELETED() TO p:\Projects\intranet\chk_dup TYPE XL5 
*
* Removed the deleted records
PACK 
*
* reset your environment
DELETE TAG UNIQUE
CLOSE DATABASES ALL
SET DELETED &lcSetDelete
RETURN
*

David W. Grewe Dave

 

[Dan Freeman]

This should get what you've said over and over you want:

SELECT model ;
FROM vupload ;
GROUP BY model ;
HAVING Count(model)>1) ;
INTO ARRAY Duplicates

Yeah, it's from the query you originally posted, but run it alone instead of as a sub-query.

 

[MikeMV]

That works great Dan,

Thanks a million.