PLS Help with query

Please help me with complicated query:
eg I have table(tbl) with 2 fields (a,b) I need to delete ALL rows where
value in "a" field is dublicate, but leave one of each. in simple sentence I
need to delete invert distinct of field "a".
Please help me with this query
Rows are NOT duplicated, just value in one of fields are
There are primary key in this table
Following the similar explanation of this table
log_id int(11) UNSIGNED auto_increment //ID
rnd_id int(11) //SOMETHING
option_id int(11) //SOMETHING
timestamp DateTime //SOMETHING
ip_addr varchar(15) //HERE ARE DUPLICATE VALUES
host varchar(70) //SOMETHING
agent varchar(80) //SOMETHING
thxHi
You may want to try something like:
DELETE FROM MyTable
FROM MyTable A
WHERE EXISTS ( SELECT * FROM MyTable B WHERE A.IP_addr = B.IP_addr and
A.log_id > B.log_id )
or
DELETE FROM A
FROM MyTable A JOIN MyTable B ON A.IP_addr = B.IP_addr and A.log_id >
B.log_id
John
"Tamir Khason" <tamir-NOSPAM@.tcon-NOSPAM.co.il> wrote in message
news:O44xqCnMEHA.3712@.TK2MSFTNGP10.phx.gbl...
> Please help me with complicated query:
> eg I have table(tbl) with 2 fields (a,b) I need to delete ALL rows where
> value in "a" field is dublicate, but leave one of each. in simple sentence
I
> need to delete invert distinct of field "a".
> Please help me with this query
> Rows are NOT duplicated, just value in one of fields are
> There are primary key in this table
> Following the similar explanation of this table
> log_id int(11) UNSIGNED auto_increment //ID
> rnd_id int(11) //SOMETHING
> option_id int(11) //SOMETHING
> timestamp DateTime //SOMETHING
> ip_addr varchar(15) //HERE ARE DUPLICATE VALUES
> host varchar(70) //SOMETHING
> agent varchar(80) //SOMETHING
> thx
>
>|||Thank you for response, but
As far as I see it will delete everything.
I need to leave one of each kind
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:eu3vAvnMEHA.556@.tk2msftngp13.phx.gbl...
> Hi
> You may want to try something like:
> DELETE FROM MyTable
> FROM MyTable A
> WHERE EXISTS ( SELECT * FROM MyTable B WHERE A.IP_addr = B.IP_addr and
> A.log_id > B.log_id )
> or
> DELETE FROM A
> FROM MyTable A JOIN MyTable B ON A.IP_addr = B.IP_addr and A.log_id >
> B.log_id
> John
> "Tamir Khason" <tamir-NOSPAM@.tcon-NOSPAM.co.il> wrote in message
> news:O44xqCnMEHA.3712@.TK2MSFTNGP10.phx.gbl...
> > Please help me with complicated query:
> > eg I have table(tbl) with 2 fields (a,b) I need to delete ALL rows where
> > value in "a" field is dublicate, but leave one of each. in simple
sentence
> I
> > need to delete invert distinct of field "a".
> > Please help me with this query
> > Rows are NOT duplicated, just value in one of fields are
> > There are primary key in this table
> > Following the similar explanation of this table
> > log_id int(11) UNSIGNED auto_increment //ID
> > rnd_id int(11) //SOMETHING
> > option_id int(11) //SOMETHING
> > timestamp DateTime //SOMETHING
> > ip_addr varchar(15) //HERE ARE DUPLICATE VALUES
> > host varchar(70) //SOMETHING
> > agent varchar(80) //SOMETHING
> >
> > thx
> >
> >
> >
>|||Hi
It will only delete those that have a higher log_id and the same Ip address.
John
"Tamir Khason" <tamir-NOSPAM@.tcon-NOSPAM.co.il> wrote in message
news:Owbc3aoMEHA.3940@.tk2msftngp13.phx.gbl...
> Thank you for response, but
> As far as I see it will delete everything.
> I need to leave one of each kind
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:eu3vAvnMEHA.556@.tk2msftngp13.phx.gbl...
> > Hi
> >
> > You may want to try something like:
> >
> > DELETE FROM MyTable
> > FROM MyTable A
> > WHERE EXISTS ( SELECT * FROM MyTable B WHERE A.IP_addr = B.IP_addr and
> > A.log_id > B.log_id )
> >
> > or
> >
> > DELETE FROM A
> > FROM MyTable A JOIN MyTable B ON A.IP_addr = B.IP_addr and A.log_id >
> > B.log_id
> >
> > John
> >
> > "Tamir Khason" <tamir-NOSPAM@.tcon-NOSPAM.co.il> wrote in message
> > news:O44xqCnMEHA.3712@.TK2MSFTNGP10.phx.gbl...
> > > Please help me with complicated query:
> > > eg I have table(tbl) with 2 fields (a,b) I need to delete ALL rows
where
> > > value in "a" field is dublicate, but leave one of each. in simple
> sentence
> > I
> > > need to delete invert distinct of field "a".
> > > Please help me with this query
> > > Rows are NOT duplicated, just value in one of fields are
> > > There are primary key in this table
> > > Following the similar explanation of this table
> > > log_id int(11) UNSIGNED auto_increment //ID
> > > rnd_id int(11) //SOMETHING
> > > option_id int(11) //SOMETHING
> > > timestamp DateTime //SOMETHING
> > > ip_addr varchar(15) //HERE ARE DUPLICATE VALUES
> > > host varchar(70) //SOMETHING
> > > agent varchar(80) //SOMETHING
> > >
> > > thx
> > >
> > >
> > >
> >
> >
>|||Hi Tamir,
I noticed that the issue was posted in
microsoft.public.sqlserver.programming with the same title. Based on my
test, John's T-SQL statements hit the right answer.
Anyway, if you have follow up questions, please post there and I will be
glad to work with you.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know.
Sincerely yours,
Michael Cheng
Microsoft Online Support
***********************************************************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.