title: Select distinct [user role] with higher view, write, execute permission
body: <p>My Users have [Roles] with View|Edit|Execute permission (all are [bit] columns).<br>
  Each User can also belong to [UserGroup] witch has its own Roles defined.</p>
  
  <p><b>What I have:</b>  </p>
  
  <p>After union both result's, I get duplicated roles from each table.</p>
  
  <p><a href="https://i.ibb.co/3YRhnpc/Duplicated-Roles.jpg" rel="nofollow noreferrer"><img src="https://i.ibb.co/3YRhnpc/Duplicated-Roles.jpg" alt="Duplicated Roles"></a></p>
  
  <pre><code>declare @Roles TABLE
  (
    RoleId int, 
    RoleCode nvarchar(max),
    CanView bit,
    CanEdit bit,
    CanExecute bit
  )
  
  insert into @Roles (RoleId, RoleCode, CanView, CanEdit, CanExecute)
  select t.RoleId, t.RoleCode, t.CanView, t.CanEdit, t.CanExecute from (
  
      -- Get User Roles
      select R.Id as 'RoleId', R.CodeName as 'RoleCode', U.CanView, U.CanEdit, U.CanExecute from TM.dbo.UserRoles U
      left join TM.dbo.Roles R on U.RoleId = R.Id
      where U.UserId = @UserId
  
      union
  
      -- Get User Group Roles
      select R.Id as 'RoleId', R.CodeName as 'RoleCode', GR.CanView, GR.CanEdit, GR.CanExecute from TM.dbo.Roles R
      inner join TM.dbo.GroupRoles GR on R.Id = GR.RoleId
      inner join TM.dbo.UserGroups UG on GR.GroupId = UG.GroupId
      where UG.UserId = @UserId
  
  ) as t
  
  select * from @Roles
  </code></pre>
  
  <p><b>Expected Result:</b>  </p>
  
  <p>The result should contain 1 row for each Role but have View|Edit|Execute permission set to the one with higher permission.</p>
  
  <p><a href="https://i.ibb.co/DY6rmvp/Expected-Roles.jpg" rel="nofollow noreferrer"><img src="https://i.ibb.co/DY6rmvp/Expected-Roles.jpg" alt="Expected Roles"></a></p>