A way to remove duplicates...maybe

[bobregion]

I found the following link:

http://www.15seconds.com/issue/011009.htm

It provides an intriguing way to remove duplicates from a database. It works fine in SQL2000 query manager. However, in FoxPro 6,7 and 8 it fails with in the following way.

Here is my example.

I have the following table named contacts:

id name phone
10 john smith 4048231234
11 john smith 4048231234
12 john smith 4048231234
20 jane doe 5155562432
30 jack brown 8082593353
31 jack brown 8082593353

I want:
id name phone
12 john smith 4048231234
20 jane doe 5155562432
31 jack brown 8082593353


Following the webpage instructions I create the SQL statement:

SELECT * from contacts WHERE exists(select null FROM contactsb WHERE b.phone = contacts.phone GROUP BY l_phone HAVING contacts.id < MAX(b.id)) )

In theory, I should have a result:

id name phone
10 john smith 4048231234
11 john smith 4048231234
20 jane doe 5155562432
30 jack brown 8082593353

In reality I get:

&quot;SQL: cannot determine datatype of SQL column: uay&quot;

When I change 'null' to '*' I get:

&quot;SQL: GROUP BY clause is missing or invalid&quot;

at this point I am scratching my head. I have verified that all of the commands in the query are valid. FoxPro simply will not parse this statement and I cannot figure out why.



I just cannot seem to make it work.

 

[craigsboyd]

Try this instead:

SELECT * from contacts ;
GROUP BY phone, name

...as for your errors, I think you have some typos (the the SQL does not however give the desired results even when written correctly as you have laid out)...

CREATE CURSOR contacts (id I, name C(30), phone c(10))
INSERT INTO contacts (id, name, phone) VALUES (10, 'john smith', '4048231234')
INSERT INTO contacts (id, name, phone) VALUES (11, 'john smith', '4048231234')
INSERT INTO contacts (id, name, phone) VALUES (12, 'john smith', '4048231234')
INSERT INTO contacts (id, name, phone) VALUES (20, 'jane doe', '5155562432')
INSERT INTO contacts (id, name, phone) VALUES (30, 'jack brown', '8082593353')
INSERT INTO contacts (id, name, phone) VALUES (31, 'jack brown', '8082593353')

SELECT * from contacts ;
WHERE exists;
(;
select * FROM contacts b ;
WHERE b.phone = contacts.phone AND b.name = contacts.name AND b.id = contacts.id ;
GROUP BY phone, name, id ;
HAVING contacts.id < MAX(b.id) ;
)

Slighthaze = NULL
_{craig1442@mchsi.com}

&quot;Whom computers would destroy, they must first drive mad.&quot; - Anon​

 

[ramani]

Hi

If you are looking for the unique records only...
Any of this should do you the job..

SELECT MAX(id) AS id, name, phone GROUP BY name,phone
OR
SELECT MAX(id) AS id, name, phone GROUP BY name
OR
SELECT MAX(id) AS id, name, phone GROUP BY phone

The Group BY should be your uniwue identifier.




____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[craigsboyd]

...I realize now that I misread your original post, you aren't looking for the unique records, instead you are looking for the duplicate records, and though you could get those duplicate records by using a NOT IN clause such as:

CREATE CURSOR contacts (id I, name C(30), phone c(10))

INSERT INTO contacts (id, name, phone) VALUES (10, 'john smith', '4048231234')
INSERT INTO contacts (id, name, phone) VALUES (11, 'john smith', '4048231234')
INSERT INTO contacts (id, name, phone) VALUES (12, 'john smith', '4048231234')
INSERT INTO contacts (id, name, phone) VALUES (20, 'jane doe', '5155562432')
INSERT INTO contacts (id, name, phone) VALUES (30, 'jack brown', '8082593353')
INSERT INTO contacts (id, name, phone) VALUES (31, 'jack brown', '8082593353')

SELECT * from contacts ;
WHERE id NOT in ;
( ;
select MAX(id) FROM contacts b ;
GROUP BY phone, name ;
)

...there should be a way to make the having clause handle it as in your example...the syntax is off a bit, I will play around with it for a little bit. Part of my confusion is the results that you say you should have in theory aren't correct...

id name phone
10 john smith 4048231234
11 john smith 4048231234
20 jane doe 5155562432
30 jack brown 8082593353

...the jan doe record in red should not have been included in your example as it is not a duplicate record.

...another thing that should be cleared up is whether you want the last physical record of each group to be considered the unique record or the record with the highest id to be considered the unique id (I am assuming the latter now given your example).

Slighthaze = NULL
_{craig1442@mchsi.com}

&quot;Whom computers would destroy, they must first drive mad.&quot; - Anon​

 

[davekirbawy]

We used under 2.6 and under vfp8

select * unique from xxxx into dbf yyyy

Dave Kirbawy

 

[craigsboyd]

...the problem with this example and getting my head around it is that it just isn't necessary to use the group by or having clause in order to facilitate this, and I am struggling to come up with an example in my head that would be more appropriate. You can get what you need with a simple WHERE clause, such as:

CREATE CURSOR contacts (id I, name C(30), phone c(10))

INSERT INTO contacts (id, name, phone) VALUES (10, 'john smith', '4048231234')
INSERT INTO contacts (id, name, phone) VALUES (12, 'john smith', '4048231234')
INSERT INTO contacts (id, name, phone) VALUES (11, 'john smith', '4048231234')
INSERT INTO contacts (id, name, phone) VALUES (20, 'jane doe', '5155562432')
INSERT INTO contacts (id, name, phone) VALUES (30, 'jack brown', '8082593353')
INSERT INTO contacts (id, name, phone) VALUES (31, 'jack brown', '8082593353')

SELECT * from contacts ;
WHERE exists ;
( ;
select &quot;&quot; FROM contacts b ;
WHERE b.id > contacts.id AND b.phone = contacts.phone AND b.name = contacts.name ;
)

Slighthaze = NULL
_{craig1442@mchsi.com}

&quot;Whom computers would destroy, they must first drive mad.&quot; - Anon​