how to show duplicates 4

[KoenPiller]

Hi,

I have this table with duplicated entrees:

recno field1 field2 field3
1 aa ab abc
2 ab ab abc
3 aa ab abd
4 ac bb abc
would like to show only recno(1) and recn(3) which are identical on field1 and field2

How to proceed?

Regards,

Koen

 

[mkrausnick]

So from what I understand you want to output all the detail from both records 1 and 3.

I assume there is no primary key for the table. In that case, you can use SQL select with grouping to create an intermediate cursor containing duplicates, then select the records indicated in that table

Something like:

SELECT field1,field2,count(*) FROM mytable GROUP BY  field1,field2 HAVING count(*)>1 INTO CURSOR cursor1
SELECT * FROM cursor1 a LEFT JOIN mytable b ON a.field1=b.field1 AND a.field2=b.field2 ORDER BY b.field1,b.field2

should work.

If there is a primary key in the table, the SQL would be slightly simpler.

Or if you're not as comfortable with SQL,
1) Index the table on field1+field2.
2) Scan the table, checking the value of field1+field2 against the previous record; if it matches, output both records.



Mike Krausnick
Dublin, California

 

[TheRambler]

Hi,

I would do it using two SELECTs:

SELECT fld1+fld2 as compFld, count(*) ;
FROM yourTable ;
GROUP BY 1 ;
HAVING count(*) > 1 ;
INTO CURSOR dups

SELECT * ;
FROM yourTable ;
WHERE fld1+fld2 in (;
SELECT compFld FROM dups)

 

[Stella740pl]

Try this (assuming your Field1 and Field2 are character):

SELECT Field1+Field2, COUNT(*) AS Cnt ;
   FROM MyTbl ;
   GROUP BY 1 ORDER BY 1 ;
   HAVING Cnt>1 ;
   INTO ARRAY Dupl

SELECT MyTbl
BROWSE LAST FOR ASCAN(Dupl, Field1+Field2)>0

 

[Stella740pl]

Oh. We all have been typing at the same time very close things.

 

[KoenPiller]

Hi,

Thanks,

2 cursors, that's it.

Regards,

Koen

 

[zhavic]

if you are using VFP 9 ( don't know if it will run in earlier versions )
that you can do it in one SELECT statement, which will be faster:

SELECT yourTable.*, Duplicates.nCount ;
  FROM  ;
	INNER JOIN ( SELECT fld1+fld2 AS join_column,;
						COUNT(*) AS nCount ;
					GROUP BY 1 ;
					FROM yourTable ;
					HAVING COUNT(*) > 1 ;
			   ) AS Duplicates ON Duplicates.join_column = yourTable.fld1 + yourTable.fld2

or this one is standad query for this purpose and also will
run if fields fld1 and fld2 are not the same type

SELECT yourTable.*, Duplicates.nCount ;
  FROM  ;
	INNER JOIN ( SELECT fld1, fld2, ;
						COUNT(*) AS nCount ;
					GROUP BY fld1, fld2 ;
					FROM yourTable ;
					HAVING COUNT(*) > 1 ;
			   ) AS Duplicates ON Duplicates.fld1 = yourTable.fld1 ;
			   						AND Duplicates.fld2 = yourTable.fld2

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.