Can anyone have a suggestion for a dup records based on this conditions ?

[titoneon]

Hello everyone,
i have table named newtemp with these fields
Custno(chr), invdte (date) and price(currency)
i have in this table duplicated records, that is fine but what is not correct is the
ones that are duplicated but have the same custno, invdate and same price values, so i need to delete just one of
them

example

custno invdte price
1001 02/12/12 $45.00
1001 03/10/13 $35.00
2003 01/08/12 $15.00
2003 01/08/12 $15.00

the last two records shown are having the same values in each field, i just need to find
and SQL or a little piece of code just to delete just one of the two record when this condition happens
in the table.
Does anyone has a suggestion?
Thanks a lot in advance
Ernest

 

[TamarGranor]

Check out the section "Dealing with duplicates" in this paper:

http://www.tomorrowssolutionsllc.com/Conference Sessions/Solving Common Problems with VFP's SQL.pdf

Tamar

 

[Olaf Doschke]

As these three fields are the only fields in your newtemp table, and as it's called newtemp, I assume this is a query result.
Then you could simply add the key word DISTINCT to the query as in SELECT DISTINCT f1,f2,f3 from ...
Even if it's not a query result, you can now SELECT DISTINCT * FROM newtemp Into Cursor curTemp

Bye, Olaf.

 

[DSummZZZ]

If the entire record is duplicated, it isn't that difficult to remove the second (or third, or fourth, ...) record. You need to have an index on the relevant fields, then scan through the table checking to see if all the fields are in fact, duplicated. If so, then just get rid of the second record.
This code works for me:

STORE 0 TO nCounter
DO WHILE !EOF()
   STORE .t. TO lDupe
   SCATTER TO aOld
   STORE RECNO() TO nLastGood
   SKIP 
   SCATTER TO aCurrent
   FOR zzz = 1 TO ALEN(aOld)
      IF aCurrent[zzz] # aOld[zzz]
         *... if any field is different, it's not a dupe
         lDupe = .F.
         EXIT 
      ENDIF 
   NEXT       
   IF lDupe   &&... if dupe, get rid of it
      nCounter = nCounter + 1 
      BLANK
      DELETE 
      GOTO nLastGood
      WAIT WINDOW EVALUATE(KEY()) + " - " + 'Deleted ' + TRANSFORM(nCounter) NOWAIT 
   ENDIF 
ENDDO 
?'Deleted ' + TRANSFORM(nCounter) 
RETURN

You can also add fields to not check if you want, by adding something like

IF EVALUATE(aCurrent) == "field_to_skip"
   LOOP
ENDIF

-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[titoneon]

Hi,
All of these solutions are working but I need to group by custno and order by price, and the group does not want to work with distinct,
what i m doing wrong ?
this what i have:

SELECT distinct custno,company, invdte, sum_bextpr AS price;
FROM newtemp GROUP BY custno ORDER BY price INTO CURSOR newrec

the error i got is "group by clause is missing or invalid

Thanks a lot

 

[Olaf Doschke]

If you group by, you don't need distinct, you can't combine them. But you need to group by custno AND invdte and any other field you don't aggregatte (via SUM,AVG,MIN,MAX or any aggregation function).

Therefore:

SELECT custno,company, invdte, sum_bextpr AS price;
FROM newtemp GROUP BY custno,company, invdte ORDER BY price INTO CURSOR newrec

Bye, Olaf.

 

[titoneon]

I just wanted to thank you all for the help, Thanks Tamar for the link, that is good so i can learn from there, Olaf and the rest thanks a lot, you got it, i got it
Ernesto

 

[JRB-Bldr]

I guess I am not sure if you want to remove the duplicates from the data table, or merely create a new data cursor with non-duplicate values

For the first I would typically use a temporary Index with the UNIQUE option to remove duplicates like this.

For Example:

USE MyTable in 0 EXCL
SELECT MyTable
DELETE ALL
* --- Build Unique Index on desired criteria ---
INDEX ON custno + DTOS(invdate) + STR(price) [b]UNIQUE[/b] TAG Uniq
* --- Recall all Unique records 'seen' within Uniq Index ---
RECALL ALL
SET ORDER TO
* --- Get Rid of Temporary Index ---
DELETE TAG Uniq
* --- Remove Duplicate Records ---
PACK

For the later you are close with your SQL Query approach

I agree with Olaf in that you do not need Both - DISTINCT and GROUP BY.

But I am not sure why you are using your GROUP BY clause.
You do not show anything in your query code that would require a GROUP BY (such as SUM(), COUNT(), AVERAGE(), MIN(), MAX(), etc.)

Maybe you meant for it to be something like the following:

SELECT custno,;
    company,;
    invdte,;
    SUM(bextpr) AS price;
    FROM newtemp ;
    GROUP BY custno;
    ORDER BY price ;
    INTO CURSOR newrec

Good Luck,
JRB-Bldr

 

[titoneon]

Jrb,

what i need, is to make sure that i can remove from the newtemp.dbf only those records that the custno, invdte and sum_bextpr are equal, but also will be other records with the same custno but different invdte and sum_bextpr that i need to keep, then i want to create a cursor that can just show the custno once and summarize the total on sum_bextpr, i don't care what will be in the new cursor the invdte, so i guess i need to use once sql with distinct to get rid of the duplicates and then the second sql option you presented in your post, unless is there a way to combine just one sql
to do all i need at once
Thanks
Ernesto

 

[Olaf Doschke]

> then i want to create a cursor that can just show the custno once and summarize the total on sum_bextpr, i don't care what will be in the new cursor the invdte
Of course that needs a second SQL, you ca't have two different groupings in one SQL.

But you can also use a table as you already have WITH duplicates and group them via report groups. If you don't use the detail band of a report, but only the group footer. That means you use report variables to sum and print them in the group footer. You can nest two groups, the inner one groups by custno and invdte, the outer group just groups by custno. You use two separate report variables summing bextpr and reset one of them at the change of the inner group, while you reset the other one only at change of the outer group.

The report data cursor just has to be sorted by custno and invdte.

This is essentially differing from the grouping SQL does. In grouping means, you have one record per group in the result. You can't nest the groups of an sql query, because you can't have a record per inner and per outer group at the same time. Either you aggregate on the level of dates and have a record per date and multiple ones per custno, in the general case, OR you group by custno only, and then of course don't have a record per date. You can't have both in one query, how? You have to comprehend these are two seperate ways of grouping, even though the main principle of group changes applies to both. GROUP BY field1,field2,field3 doesn't cause three groups, it causes a change of group, if either of those three fields changes. And then result then summarizes data having the same triple of values into one record. You can't have 1 and N records at the same time.

And the nesting of groups possible in a report also has it's limitations, eg you can't nest weekly data in monthly data, as months don't always end at weekends, so the inner group needs to be fully contained inside the outer group for the report grouping.

Bye, Olaf.

 

[titoneon]

Olaf,
Thanks a lot i understand, yes i will do the grouping as you said in the report, so i will group by custno and sum the price there so if i have a 3 records with same custno, now in the report it will be shown as one record with that custno and the total amount that is the result of sum(each price for record with the same custno)
Thanks a lot for your imput and your help
Ernesto