CODEWITHSUNDEEP
sql-server
Rex_C
Rex_C

Reputation: 2477

Inner join where a particular column needs to be distinct

I'm writing the following stored procedure:

SELECT invite.sender_id,
       invite.invite_msg_text,
       invite.date_sent,
       person.name_first,
       person.name_last,
       member.member_image_url
FROM   invite
       INNER JOIN person
               ON invite.sender_id = person.person_id
       INNER JOIN member
               ON invite.sender_id = member.person_id
WHERE  invite.invite_status = 'P'
       AND invite.recipient_email = @recipientEmail
ORDER  BY invite.date_sent DESC

but I want to return rows with distinct sender_id/person_id. Right now it will return duplicate rows if a user has sent more than one invite. Is it possible to return only distinct rows based on sender_id/person_id

Upvotes: 2

Views: 41

Answers (2)

JamieD77
JamieD77

Reputation: 13949

This should give you the most recent invite sent by sender_id.. Using row_number to determine which was sent last.

SELECT * FROM 
(
    SELECT invite.sender_id,
           invite.invite_msg_text,
           invite.date_sent,
           person.name_first,
           person.name_last,
           member.member_image_url,
           ROW_NUMBER() OVER (PARTITION BY invite.sender_id ORDER BY invite.date_sent DESC) Rn 
    FROM   invite
           INNER JOIN person
                   ON invite.sender_id = person.person_id
           INNER JOIN member
                   ON invite.sender_id = member.person_id
    WHERE  invite.invite_status = 'P'
           AND invite.recipient_email = @recipientEmail
) t
WHERE t.RN = 1
ORDER BY t.date_sent DESC

Upvotes: 1

cronos2546
cronos2546

Reputation: 1106

SELECT invite.sender_id, invite.invite_msg_text, invite.date_sent, person_a.name_first, person_a.name_last, member.member_image_url
FROM invite
INNER JOIN (select person_id, max(name_first) as name_first,max(name_last) as name_last from person group by person_id) as person_a 
ON person_a.person_id = invite.sender_id

Should accomplish what you seem to be after.

Upvotes: 0

Related Questions