cleaning up duplicates

I have a table with column a, b, c, and d
column b has several duplicates and I want to clean this up.
a b c d
=============
1 name1 aa gg
2 name2 bb hh
3 name3 cc ii
4 name3 dd jj
5 name4 ee kk
6 name5 ff ll
etc.
I'm guessing I would need to select all the unique rows from the table,
put them into temp_table1, select the rows with the duplicates as
single rows into temp_table2, and join those two back into the original
table.
would this be the right idea? and if so,
exactly how would I accomplish this?
any help would be greatly appreciated!create table #test
(
col1 int not null primary key,
col2 varchar(10) not null,
col3 char(1) not null
)
insert into #test values (1,'name1','a')
insert into #test values (2,'name2','b')
insert into #test values (3,'name2','y')
insert into #test values (4,'name3','c')
insert into #test values (5,'name4','f')
select * from #test
where col1=(select max(col1) from #test t where t.col2=#test.col2)
order by col1
<guilesf2@.hotmail.com> wrote in message
news:1141542444.740452.81000@.e56g2000cwe.googlegroups.com...
>I have a table with column a, b, c, and d
> column b has several duplicates and I want to clean this up.
> a b c d
> =============
> 1 name1 aa gg
> 2 name2 bb hh
> 3 name3 cc ii
> 4 name3 dd jj
> 5 name4 ee kk
> 6 name5 ff ll
> etc.
>
> I'm guessing I would need to select all the unique rows from the table,
> put them into temp_table1, select the rows with the duplicates as
> single rows into temp_table2, and join those two back into the original
> table.
> would this be the right idea? and if so,
> exactly how would I accomplish this?
> any help would be greatly appreciated!
>|||thanks a lot! just for my understanding, what would be the reason the
table
needs that alias-type thing in order to work? just curious and trying
to learn.
again, thanks a lot|||If your goal is to cleanup only column b then you probably need to
normalize the table further. You will need to store unique names in a
separate table called NAMES (id, name) and store the id in column b.sqlsql