Delete Single Instance of Redundant Rows 1

[jtheis]

I am trying to figure out how to delete one instance of a redundant row from a table with a single column. For example, in table QueuedLoads I have one column, LoadNumber. The value in LoadNumber can (and will often be) the same as other rows in the table.

Table: QueuedLoads

LoadNumber
34389
34389
34389
4756
4756
1234

What I want to do is just delete ONE of the duplicate rows based on the user specifying which LoadNumber to delete. So I want to delete just one of the 34389 when I execute the query.

There is no preference as far as which row gets deleted, as it is only a single column table. I've seen a lot of examples of how to get rid of ALL of the redundant rows, but I need to keep all but one of the redundant rows.

Thanks for any help!

 

[vongrunt]

Add identity column, delete rows with MAX() value per group (where group has dupes), remove identity column.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLSister]

I'd say to add an identity column, then select the max identity grouping on Loadnumber into another table. rename the old table then rename the new table with the original name, drop the old table.

the select would be something like:

Select LoadNumber, Max(myID) into QueuedLoads2 from QueuedLoads group by LoadNumber

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[vongrunt]

Requirement was to remove one dupe, not to dedupe everything

create table blah ( LoadNumber int )
insert into blah select 34389 union all select 34389 union all select 34389 union all 
	select 4756 union all select 4756 union all select 1234

alter table blah add idcol int identity(1, 1)
go

-- backup data :X and cut here
delete from blah
where idcol in 
(	select max(idcol)
	from blah
	group by LoadNumber
	having count(*) > 1 -- change to 2 if necessary
)

select *
from blah
order by LoadNumber desc
-- cut here

drop table blah

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[jtheis]

vongrunt,

Perfect! That works exactly how I need it to work. Definitely worth a star!

 

[vongrunt]

Btw. drop identity column after everything

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLDenis]

You might want to add a unique constraint to that table so that you don't have to go through this trouble again in the future

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/