deduping a table

[ashab02]

Hello
I have a table with loads of records. What I am trying to do is dedpue the table. I have details in this table but the records can sometimes be slighly different from the previous record.
An example would be:

ID Field1 Field2
1 Joe Bloggs
2 Joe Blogger
3 Joe Blog

Here I just want to select the first record and ignore the other.
I have tried difference function but can't get it to work.
Can somebody give me a hand on this?

 

[jbenson001]

Here I just want to select the first record and ignore the other.

Select <cols>
From <Your Table>
Where Field1 = 'Joe' AND Field2 = 'Bloggs'

 

[Joulius]

Hi

Here's a starting point for you:

DECLARE @TEST TABLE (ID int identity(1,1), Field1 varchar(55), Field2 varchar(55))

INSERT INTO @TEST (Field1, Field2)
SELECT 'Joe', 'Bloggs' UNION ALL
SELECT 'Joe', 'Blogger' UNION ALL
SELECT 'Joe', 'Blog' UNION ALL
SELECT 'John', 'Loggs' UNION ALL
SELECT 'John', 'Logger' UNION ALL
SELECT 'John', 'Log'

SELECT * FROM @TEST

DELETE T
FROM @TEST T
LEFT JOIN (
	SELECT	MIN(ID) as FirstID,
			SOUNDEX(Field1) as SF1,
			SOUNDEX(Field2) as SF2
	FROM @TEST 
	GROUP BY SOUNDEX(Field1), SOUNDEX(Field2)
) DUPES ON T.ID = DUPES.FirstID
WHERE DUPES.FirstID IS NULL

SELECT * FROM @TEST

but given your example, it seems you'll need a different SOUNDEX algorithm.