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

Deletetion of an old Remote Server

I am trying to set up a publisher and I get an Error 18482: Could not
connect to server 'servername' because 'WEB1' is not defined as a remote
server. I went to the Remote Servers where WEB1' is listed, although this
server is no longer on our network. When I try to delete the listing, I get
the Error 15010: The server 'WEB1' does not exist. Use sp_helpserver to sho
w
available servers. I do this and it is not listed of course. How do I
remove this server with a query?
Thank you.Do you mean linked server?
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:CFBDDA35-61A5-4CF0-9899-D15B8AC63D32@.microsoft.com...
> I am trying to set up a publisher and I get an Error 18482: Could not
> connect to server 'servername' because 'WEB1' is not defined as a remote
> server. I went to the Remote Servers where WEB1' is listed, although this
> server is no longer on our network. When I try to delete the listing, I
get
> the Error 15010: The server 'WEB1' does not exist. Use sp_helpserver to
show
> available servers. I do this and it is not listed of course. How do I
> remove this server with a query?
> Thank you.|||No, it is listed under Remote Servers, not under Linked Servers.
"Jack Vamvas" wrote:

> Do you mean linked server?
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:CFBDDA35-61A5-4CF0-9899-D15B8AC63D32@.microsoft.com...
> get
> show
>
>

Delete replication

I defined replication in my SQL Server 2000. I have deleted everything except
a remote server. I try to delete this and I get the following error:
Microsoft SQL_DMO (ODBC SQLState:42000) Error 15190: There are still remote
logins for the server "Name of the server".
Where can I find these remote logins? I had a look at the remote server and
I can not see any login.
Thanks
This is probably the distributor_admin account. I would not touch this
account even if you don't require replication anymore.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Laura Pradre" <LauraPradre@.discussions.microsoft.com> wrote in message
news:0F20D434-1D9F-43C2-A53B-3710B6439C75@.microsoft.com...
>I defined replication in my SQL Server 2000. I have deleted everything
>except
> a remote server. I try to delete this and I get the following error:
> Microsoft SQL_DMO (ODBC SQLState:42000) Error 15190: There are still
> remote
> logins for the server "Name of the server".
> Where can I find these remote logins? I had a look at the remote server
> and
> I can not see any login.
> Thanks