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

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

insert #p values (
'Raly JunkD',     '83 NW St',         '')

insert #p values (
'Bill Gtea',   NULL ,         '')
insert #p values (
'Bill Gtea',    '67 Village Blvd',   '')

-- 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, ''))

This page was last updated on May 01, 2006 04:28 PM.