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')
select * from #p t1 where Not exists(SELECT * FROM #p t2 WHERE t1.Phone = t2.Phone And t2.Name + COALESCE(t2.Address, '') > t1.Name + COALESCE(t1.Address, '')) |