How to exclude duplicate records

Here is simple query which shows simple technique to exclude duplicate records from output. In my example I am selecting only first record from duplicate list based on Primary key.

Select * into #tmp1
From(
select 1 as id,'aaa' as name,11 as age 
UNION ALL
select 1 as id,'aaa' as name,12 as age 
UNION ALL
select 2 as id,'bbb' as name,22 as age 
UNION ALL
select 2 as id,'bbb' as name,23 as age 
UNION ALL
select 3 as id,'ccc' as name,33 as age 
) a


select a.*,b.* 
from #tmp1 a 
LEFT JOIN 
(   
   SELECT 
	   ROW_NUMBER() OVER(PARTITION BY id ORDER BY id,name) AS Row
	  ,id,name,age
   FROM 
   ( 
	  SELECT id ,name,age
	  FROM #tmp1
	  where id IN ( SELECT id FROM #tmp1 group by id having count(*)>1 ) 
   ) all_dups 
) b --//dups with counter
ON a.id=b.id and a.name=b.name and a.age=b.age --//todo add many more ....
Where b.Row IS NULL or b.Row=1

go

drop table #tmp1

Binary World is a Software Development company located in Atlanta, USA (since 2007). Binary World specialized in Business Intelligence, mobile, cloud computing and .Net Application Development.

Tagged with: ,
Posted in SQL Server, T-SQL Tips

Leave a Reply