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

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

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)