SQLTeam.com | Weblogs | Forums

Distinct on two columns only

I have a table as below.

Id username userid documenttype
1 abc 101 type1
2 abc 102 type2
3 abc 101 type3
4 def 102 type3
5 ghi 103 type4

I need to perform distinct on two columns (username and userid) and store it into a new table

Id username userid
1 abc 101
2 abc 102
3 def 102
4 ghi 103

Try this one
Declare @temp table ( Id int
,username varchar(20)
,userid varchar(5)
,documenttype varchar(10)

Insert into @temp values (1 ,'abc', '101','type1'),

Select ID,username,userid from (
Select ID
,row_number()over (partition by username,userid order by id Asc) as rw
from @temp) a where rw=1