>The company table has a name column char(25) and a comp_code column char(4).
>The comp_code column is unique, but there are duplicates in the name column.
>
>I'm trying to design a query that will give the name once and the comp_code associated with that name.
>For duplicates, it doesn't matter which comp_code appears, so long as it is associated with that name.
>Any ideas?

;with cte as (select *, row_number() over (partition by [name] order by comp_code) as Rn from Companies)

select * from cte where Rn = 1 -- only one of the duplicate names will be displayed with min code

There is also a new article in TechNet Wiki for TechNet Guru competition

http://social.technet.microsoft.com/wiki/contents/articles/22706.how-to-remove-duplicates-from-a-table-in-sql-server.aspx