Delete duplicate rows

[tlaksh]

how can I delete duplicate rows from the table keeping only one row that has the max id. My table structure is like this

id Iscomputer ComputerName

The computername col has duplicate values. I need to keep only one row for each computername where iscomputer = 1. If the is computer value is 0 then I can have duplicate values. Also I want to keep the row that has the max id


Can anyone help me with this...thanks a ton.

vidya

 

[Cagliostro]

try something like:
delete from yourtable where
id in
(
select id from
(
select id, computername,
(
select count (computername) x
from yourtable t
where t.id < yourtable.id
and t.computername < yourtable.computername
) x
from yourtable
) t where t.x > 1
) Ion Filipski

filipski@excite.com

 

[tlbroadbent]

Check the MS Knowledgebase article at

http://support.microsoft.com/support/kb/articles/q139/4/44.asp.

Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[Cagliostro]

short change
where t.id < yourtable.id
should be
where t.id >= yourtable.id Ion Filipski

filipski@excite.com