How to eliminate duplicated records?

Hello,

I am writing a conversion tool to convert DBF files (FoxPro) for SQL-Server, but I bumped with a barrier that I cannot overtake by myself, so I am asking for some help.

The problem is that when I SELECT the database, I get a lot of duplicated records (the file has no primary key, then the duplication is in the identifier field too - don't ask me how it can work, because I don't know!).

If I use SELECT DISTINCT I can discard the identical records, but still this way, some duplications remains, when the records are not exactly the same.

For instances, supose I have:

001 Cadillac
001 Cadillac
001 cadilac
001 Cadillac

The second and the fourth entries will be discarded by the SELECT DISTINCT, but the third will remain.

What I would like to do is to select, using as criteria just the first column. In the case above, I would like that just the first 001 record would be accepted and ALL others would be discarded, don't matter what's there.

There is a way to do that?

Thanks!

I am assuming that your pulling the table into SQL before loading the destination table. If so this might work...

Insert Into DestinationTable
(.......)
Select ...... From SourceTable
Where left(ID,3) not in (Select left(ID,3) from DestinationTable)

Worth a shot.

Try to see if this works:

SELECT UPPER(Field1), UPPER(Field2)
FROM Table1
GROUP BY UPPER(Field1), UPPER(Field2)

Mike

mike i think your query won't give him what he need since cadilac is different from cadillac i think using group by in field1 is enough


SELECT Field1, max(Field2) FROM Table1 GROUP BY Field1


:) correct me if i'm wrong

but do u want to get the first and left the other as they are?
or do u want to take the first and delete the other?

in the second case i think u have to open your record:
SELECT * FROM yourTABLE WHERE yourCODE = '001'

and then make while:WHILE yourREC.recordcount >1
yourREC.movelast
yourrec.Delete adAffectCurrent
wend... if i get what u want

mike i think your query won't give him what he need since cadilac is different from cadillac i think using group by in field1 is enough


SELECT Field1, max(Field2) FROM Table1 GROUP BY Field1


:) correct me if i'm wrong

I would like to thanks all tries, but the one that really reached what I was looking for, was the above one!

Thanks, Duane, it was bull's eye!

;) Got Cha

mike i think your query won't give him what he need since cadilac is different from cadillac i think using group by in field1 is enough


SELECT Field1, max(Field2) FROM Table1 GROUP BY Field1


:) correct me if i'm wrong
Hey duana, you are great! I didn't read carefully there was a difference in the words cadilac and cadillac. Just noticed the difference in upper and lower case! Then I gave the wrong answer! :p

Mike