SQL: How can I take only the first match for each in an inner join?

Question

Say I have these tables:

USER

userId | partnerCode

PARTNER

partnerId | readableName | ... | partnerCode | ...

And we want:

select USER.userId, PARTNER.readableName
from USER
inner join PARTNER
on PARTNER.partnerCode = USER.partnerCode
where USER.userId = <someUser>;

However, for each userId:partnerCode pair in USER, there can be many PARTNER entries that have that partnerCode. We only care about taking the first PARTNER.readableName that we find in the join'd table (and order doesn't matter).

How can I query thusly? Thanks a lot!

Answer 1

In most databases, you can do this using the ANSI standard window function row_number():

select u.userId, p.readableName
from USER u inner join
     (select p.*,
             row_number() over (partition by p.partnerCode order by p.partnerCode) as seqnum
      from PARTNER p
     ) p
     on p.partnerCode =  u.userCode and seqnum = 1
where u.userId = <someUser>;

Given the structure of your query, it would often be faster to use a correlated subquery:

select u.userId,
       (select p.readableName
        from PARTNER p
        where p.partnerCode =  u.userCode 
        fetch first 1 row only  -- order doesn't matter, so there is no order by
      ) as readableName
from USER u
where u.userId = <someUser>;

For optimal performance, you want indexes on user(userId) and partner(partnerCode, readableName).