T-SQL Find first occurrence of unique combinations

Question

I have a table that looks like this

Log_ID  User_ID  Line   Attribute
1       A        1      **** 
1       B        2      ****
1       B        3      ****
2       C        1      ****
2       C        2      ****
2       A        3      ****
2       B        4      ****

For each Log_ID, there are multiple values in User_ID and Line. (Log_ID, Line) will always be unique, but (Log_ID, User_ID) will not.

I'm trying to return the unique (Log_ID, User_ID) pairs where the lowest Line value is the tiebreaker. The result set would look like this:

Log_ID  User_ID  Line   Attribute
1       A        1      ****
1       B        2      ****
2       C        1      **** 
2       A        3      ****
2       B        4      ****

Nothing I've tried has worked. I keep either getting unique (Log_ID, User_ID, Line) triplets or only getting rows where Line=1.

I need additional attributes from the table besides Log_ID, User_ID, and Line, so I can't just use SELECT DISTINCT

Any ideas? The solutions I've found generally assume that I'm trying to join to the table and that I want to join on the lowest match. But this is my primary table.

Thanks!

Answer 1

This type of prioritization can make good use of row_number().

select t.*
from (select t.*,
             row_number() over (partition by log_id, user_id
                                order by line) as seqnum
      from t
     ) t
where seqnum = 1;

EDIT:

You can also do this by joining on the lowest match or using a correlated subquery. For example:

select t.*
from t
where t.line = (select min(t2.line)
                from t t2
                where t2.log_id = t.log_id and t2.user_id = t.user_id
               );

row_number() is usually faster.