Remove duplicate fields from a temp table that has no primary key

Question

I need to remove duplicate fields from a temp table where the fields in question are not exactly identical.

For example, I have the following data:

First  Last     DOB
John   Johnson  10.01.02
Steve  Stephens 23.03.02
John   Johnson  2.02.99
Dave   Davies   3.03.03

Here, there are two John Johnson's. I only want to have one John Johnson - I don't care which one. So the resulting table will look something like:

First  Last     DOB
John   Johnson  10.01.02
Steve  Stephens 23.03.02
Dave   Davies   3.03.03

I'm using TSQL, but I would prefer to use SQL that is non-proprietary.

Thanks

Answer 1

Sql Server supports Common Table Expression and Window Functions. With the use of ROW_NUMBER() which supplies rank number for every group, you can filter out records which rank is greater than one (this are duplicates one)

WITH records
AS
(
    SELECT  [First], [Last], DOB,
            ROW_NUMBER() OVER (PARTITION BY [First], [Last] ORDER BY DOB) rn
    FROM    TableName
)
DELETE FROM records WHERE rn > 1

• SQLFiddle Demo
• TSQL Ranking Functions

Answer 2

You can use a CTE with ROW_NUMBER:

WITH CTE AS
(
   SELECT RN = ROW_NUMBER() OVER (PARTITION BY First, Last ORDER BY First,  Last)
   FROM TempTable
)
DELETE CTE 
WHERE RN > 1;

DEMO

Answer 3

Well, I'm late to the party, but here is a database agnostic solution:

SELECT A.*
FROM YourTable A
INNER JOIN (SELECT [First], [Last], MAX(DOB) MaxDob
            FROM YourTable
            GROUP BY [First], [Last]) B
    ON A.[First] = B.[First] 
    AND A.[Last] = B.[Last]
    AND A.DOB = B.MaxDob

And here is a sqlfiddle with a demo for it. (Thanks @JW for the schema of the fiddle)

Answer 4

You can use CTE with ROW_NUMBER() to accomplish this:

WITH CTE
AS
(
    SELECT 
       First, 
       Last, 
       DOB,
       ROW_NUMBER() OVER (PARTITION BY First, Last ORDER BY DOB) RN
    FROM  
       Table1
)

DELETE FROM CTE WHERE RN > 1

SQL FIDDLE DEMO