SQLTeam.com | Weblogs | Forums

Distinct on two columns only


#1

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


#2

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'),
(2,'abc','102','type2'),
(3,'abc','101','type3'),
(4,'def','102','type3'),
(5,'ghi','103','type4')

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