469,950 Members | 1,381 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,950 developers. It's quick & easy.

Howto: Delete every second record if duplicates

Hi.

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 ?

Thank you very much !
Alex

Jul 19 '07 #1
2 5268
Radu (cu*************@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jul 19 '07 #2
On Thu, 19 Jul 2007 20:28:34 -0000, Radu wrote:
>Hi.

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 ?

Thank you very much !
Alex
Hi Alex,

Your mention of a union makes me suspect that you don't want to remove
duplicate rows from a base table, but rather not include duplicate rows
in a view or query, without changing base data. If this suspicion is
correct, then try

SELECT PIN, Name, MIN("Added Date")
FROM YourView;

If I'm incorrect and you want to actually remove duplicated rows from a
base table, then see Erland's reply.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Jul 20 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Alexander Anderson | last post: by
16 posts views Thread by Philip Boonzaaier | last post: by
8 posts views Thread by Steve | last post: by
4 posts views Thread by DEWright_CA | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.