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