Remove duplicate fields from a temp table that has no primary key
sql, sql-delete, sql-server, t-sql
Solution
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)
Problem
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