Tuesday, January 4, 2011

Remove Dupicates using row_number()

select * from (
select row_number()over(partition by ur.roleid order by ur.roleid )tid ,UR.ROLEID,ROLENAME,U.COMPANYID,U.MERCHANTID,ISDEFAULT,U.USERID
 from rolemaster rm      
inner join userroles ur
on rm.roleid=ur.roleid
inner join users u
on ur.userid=u.userid where isdefault = 1 and rm.roleid > 5 )a where a.tid =1

No comments:

Post a Comment