Re: Deleting duplicate records based on lowest number
- From: "Chris2" <rainofsteel.NOTVALID@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 10 Oct 2005 16:31:42 -0700
"Net" <Net@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:BE9D48C9-AFAA-40EA-93B2-3ED8C0709858@xxxxxxxxxxxxxxxx
> Can some one assist with deleting duplicate records based on lowest
number.
> If field F1 are equal then only keep the record with the highest
value in
> field F0.
>
> This module works partially. If there are more than 2 duplicates
for the
> same field F1 I have to run this module again and again until all
duplicates
> are deleted.
>
<snip>
>
>
> Thanks in advance,
Net,
Ok, I'm not 100% clear on what your asking, so I'm interpreting.
It sounds like you want to delete all rows where column F1 has
duplicates, except the row where column F0 has the maximum value for
all occurences of a duplicate F1 value. (I realize that the thread
title says lowest number, but the code is all referring to "max").
Column F0 may not have duplicate values, and its values must be
ascending (for max to work; otherwise, use min for descending values).
CREATE TABLE tblDUP
(F0 AUTOINCREMENT
,F1 DATETIME
,CONSTRAINT pk_tblDUP PRIMARY KEY (F0)
)
Sample Data
F0 F1
1 01/01/2005
2 01/01/2005
3 01/01/2005
4 01/02/2005
5 01/02/2005
6 01/03/2005
7 01/04/2005
This query should get rid of rows 1, 2, and 4.
DELETE *
FROM tblDUP AS D0
WHERE D0.F0 <>
(SELECT MAX(D1.F0)
FROM tblDUP AS D1
WHERE D1.F1 = D0.F1
GROUP BY D1.F1
HAVING COUNT(D1.F1) > 1);
And, when we execute it, the data comes up:
F0 F1
3 01/01/2005
5 01/02/2005
6 01/03/2005
7 01/04/2005
Again, my apologies if I did not correctly understand your questions
or needs.
Sincerely,
Chris O.
.
- References:
- Prev by Date: Re: Help
- Next by Date: Record Size
- Previous by thread: Deleting duplicate records based on lowest number
- Next by thread: HELP!
- Index(es):
Relevant Pages
|
|
