Filter duplicates in a view

Transact-SQL
1

We have a view for reporting users. Users should have a single attribute but several users have more than two attributes and return a distinct record for each attribute.

Is there a way to sort the view by users then attribute then return only the top row for each user? Do this all in a view?

I'd post the view in full but it is huge and may be proprietary to the vendor so there's that. This is an approximation of the query

Select username, attribute from dbo.table where clientid = @clientID

How about a secondary view of the view? Would this be any help?

Sam

2

Here are 2 ways of doing it:

select username
      ,attribute
  from (select username
              ,attribute
              ,row_number() over(partition by username
                                     order by attribute
                                )
               as rn
          from dbo.table
         where clientid=@clientid
       ) as a
 where rn=1
;

select top(1) with ties
       username
      ,attribute
  from dbo.table
 where clientid=@clientid
 order by row_number() over(partition by username
                                order by attribute
                           )
;
3

Many thanks. I'll give this a try this evening!

Sam