Binaryworld - Get only one matching row for each name from several address rows. ... [ SQL Server -> T-SQL ]

This is the fastest way to get say only one matching address row for each person out of a set of duplicates. This logic assumes that you do not care which address row you want to display. It can be easily extended to include other checks say based on the last added address row etc.

Click here to copy the following block

create table #P (
Name varchar( 30 ) ,
Address varchar( 30 ) ,
Phone varchar( 30 )
)
insert #p values (
'John Smith, M.D.', '123 East St',     '999-555-1212')
insert #p values (
'John Smith,M.D.',  '123 East Street', '999-555-1212')
insert #p values (
'John Smith, MD',   '123 East St',     '999-555-1212')

insert #p values (
'Jim Smith, M.D.', '123 Weest St',     '364-879-0657')
insert #p values (
'Jim Smith,M.D.',  '123 wee Street', '364-879-0657')

insert #p values (
'Raly JunkD',   '83 NW St',     '987-754-1243')

insert #p values (
'Bill Gtea', NULL ,     '487-348-9082')
insert #p values (
'Bill Gtea',  '67 Village Blvd', '487-348-9082')

-- You can concatenate other columns also to determine uniqueness
select * from #p t1
where Not exists(SELECT * FROM #p t2
  WHERE t1.Phone = t2.Phone And
-- COALESCE will take care of NULL in address column.
t2.Name + COALESCE(t2.Address, '') >
             t1.Name + COALESCE(t1.Address, ''))