CREATE TABLE MyTable (MainKey C(6),No_in_row I) INSERT INTO MyTable VALUES ('JKT001', 10) INSERT INTO MyTable VALUES ('JKT001' , 20) INSERT INTO MyTable VALUES ('JKT002' , 10) INSERT INTO MyTable VALUES ('JKT003' , 10) INSERT INTO MyTable VALUES ('JKT003' , 20) INSERT INTO MyTable VALUES ('JKT003' , 30) INSERT INTO MyTable VALUES ('JKT003' , 30) INSERT INTO MyTable VALUES ('JKT003' , 40) INSERT INTO MyTable VALUES ('JKT003' , 40) select DISTINCT MainKey, ; IIF(count(No_in_row) > 1, "Yes", "No ") Duplicate, ; IIF(count(No_in_row) = 1, PADC("N/A",10), PADL(No_in_row,10)) No_in_row ; from MyTable; group by MainKey,No_in_row; into cursor crsDupsbut it report
Mainey Duplicate No_in_Row JKT001 No N/A JKT002 No N/A JKT003 No N/A JKT003 Yes 30 JKT003 YES 40Add:
SELECT * FROM crsDups WHERE Duplicate="Yes" OR ; MainKey NOT IN (SELECT MainKey FROM crsDups WHERE Duplicate="Yes"); into cursor crsDups1Fabio