Deleting min value from grouped records

I have a table where no keys are currently defined, so we have dups...kind
of. In this table the account number with be the primary key, and we also
have a date field. There are records in there that have the same account
number, but a different date. I want to find the duplicates, which is the
easy part. Then from there I want to delete the record that has the oldest
date. Example
record 1
account 1
date 1-1-2004
record 2
account 1
date 5-1-2004
I want to delete record 1. I am having trouble coming up with the code.
Any help is appreciated.
ThanksTry,
delete t1
where exists(select * from t1 as a where a.account_id = t1.account_id and
a.col_date > t1.col_date)
This will not eliminate duplicated rows with same col_date.
AMB
"Andy" wrote:

> I have a table where no keys are currently defined, so we have dups...kin
d
> of. In this table the account number with be the primary key, and we also
> have a date field. There are records in there that have the same account
> number, but a different date. I want to find the duplicates, which is the
> easy part. Then from there I want to delete the record that has the oldes
t
> date. Example
> record 1
> account 1
> date 1-1-2004
> record 2
> account 1
> date 5-1-2004
> I want to delete record 1. I am having trouble coming up with the code.
> Any help is appreciated.
> Thanks|||Delete SomeTable
from SomeTable
INNER JOIN
(
Select MIN([Date]), account from SomeTable
Group by account
) Subquery
on
Subquery.record = SomeTable.Record AND
Subquery.account = SomeTable.account AND
Subquery.[date] = SomeTable.[date]
HTH, Jens SUessmeyer.
"Andy" <Andy@.discussions.microsoft.com> schrieb im Newsbeitrag
news:4F5EBDA6-6BAE-44D0-9413-869F150B3640@.microsoft.com...
>I have a table where no keys are currently defined, so we have dups...kind
> of. In this table the account number with be the primary key, and we also
> have a date field. There are records in there that have the same account
> number, but a different date. I want to find the duplicates, which is the
> easy part. Then from there I want to delete the record that has the
> oldest
> date. Example
> record 1
> account 1
> date 1-1-2004
> record 2
> account 1
> date 5-1-2004
> I want to delete record 1. I am having trouble coming up with the code.
> Any help is appreciated.
> Thanks|||Are you sure you "want to delete the record with the oldest Date" and that's
all? W
--What if there is only one record?
-- What if there are morethan 2 records?
Most of the time what is desired is t odelete ALL BUT The most recent
record... whichis actually easier..
But...
Delete T
From Table T
Where DateCol =
(Select Min(DateCol) From Table
Where AccountNo = T.AccountNo)
-- Add this if you only want to delete when there are dupes with same
accountNo
And Exists (Select * From Table
Where AcountNo = T.AccountNo
And DateCol > T.DateCol)
This will delete all reco
"Andy" wrote:

> I have a table where no keys are currently defined, so we have dups...kin
d
> of. In this table the account number with be the primary key, and we also
> have a date field. There are records in there that have the same account
> number, but a different date. I want to find the duplicates, which is the
> easy part. Then from there I want to delete the record that has the oldes
t
> date. Example
> record 1
> account 1
> date 1-1-2004
> record 2
> account 1
> date 5-1-2004
> I want to delete record 1. I am having trouble coming up with the code.
> Any help is appreciated.
> Thanks

deleting dups and keeping only 1 row in a batch

I have asked this question before and got some great answers, I just wanted to ask this again. I can detect dups easily, is there a way to get a total number of all dups so that I can delete a certain number at a time, then check the total again for verification that that specific number of dups are gone? I'm still using 2000. If I delete dups, won't it delete the 1 row I want to keep? And I do have a unique identity column.

thx,

Kat

Kat -

You can get the dup rows using Group by with Having clause - see example:

SELECT ident = convert(int,1)

,name = 'harry'

INTO #Temp

INSERT #Temp

(ident, name)

SELECT 2, 'tom'

UNION

SELECT 3, 'dick'

UNION

SELECT 4, 'harry'

SELECT ident = max(ident), name INTO #dupids from #temp group by name having (count(*) > 1)

you can then

DELETE #Temp WHERE ident in (SELECT ident from #Dupids)

count output from the first query should match the second

Thanks

AWAL

|||

Thank you, I haven't had time to test this out but I'm sure it works.

Kat