Posted by Erland Sommarskog on 07/19/07 20:53

Radu (cuca_macaii2000@yahoo.com) writes:
> I have a "union" table which results of a union of two tables.
> Occasionally I could have duplicates, when the same PIN has been added
> to both tables, albeit at different Datees/Times, such as:
>
> PIN Name Added Date
> 100411 A 7/11/2007 10:12:58 AM
> 100411 A 7/17/2007 10:54:23 AM
> 100413 B 7/11/2007 10:13:28 AM
> 100413 B 7/17/2007 10:54:39 AM
> 104229 C 7/6/2007 2:34:13 PM
> 104231 D 7/6/2007 2:34:25 PM
> 104869 E 6/10/2007 11:59:12 AM
> 104869 E 6/22/2007 2:40:18 PM
>
> The question is - how can I delete by queries the first occurence
> (time-wise) of these duplicates - i.e. I would want to delete the
> first occurence of 100411 (A), the first occurence of 100413 (B), and
> the first occurence of 104869 (E) in the example above - records C and
> D show only once, so they are fine.
>
> Is there a MsAccess solution ? Is there a SQL-server solution ?

What about a primary key on (PIN, Name) to prevent this from happening
in the first place?

It's unclear what you with first occurrance, but I take to mean that
you mean the one with the earliest value of Added Date:

DELETE tbl
FROM tbl a
JOIN (SELECT PIN, Name, AddedDate = MIN(AddedDate)
FROM tbl
GROUP BY PIN, Name) AS b ON a.PIN = b.PIN
AND a.Name = b.Name
AND a.AddedDate > b.AddedDate

This solution is for SQL Server. For Access solutions, try another
newsgroup.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]