Get DISTINCT records on INNER JOIN SQL Server

Question

I have two tables. I have query that joins the first table to second table via INNER JOIN clause. The results show that there are redundant or duplicate results. In my example below the results have 4 records. I want to distinct the SdiID and I'm expecting to get only two records since there are only two unique records for sdiID. I tried adding on DISTINCT sdiID after the select but still I got 4 records. Please help. Here's my query.

    DECLARE @PageNumber AS INT, @RowspPage AS INT 
        SET @PageNumber = 1 
        SET @RowspPage = 20 
        SELECT totalCount = COUNT(*) OVER(), mailbox.ID as mailboxID, 
          sdi.ID as sdiID
           FROM [SDI].dbo.UserDocumentLocationOutbox mailbox 
           INNER JOIN [SDI].dbo.SDITransaction
           sdi on mailbox.SDITransactionID=sdi.ID 
         INNER JOIN [SYSDB].dbo.DocumentType doc on sdi.DocumentTypeID=doc.ID 
    where mailbox.CommunityID = '9ff10c7a-37f5-4580-9163-6ada55194ca7' 
    and mailbox.UserProfileID = 'f9791614-8cc0-42e3-87d1-53709bc1e099' 
    and doc.CommunityID = '9ff10c7a-37f5-4580-9163-6ada55194ca7' 
    and doc.Active=1 and doc.HideInMailbox=0  
    order by sdi.ProcessedDateTime desc 
OFFSET ((@PageNumber - 1) * @RowspPage) ROWS FETCH NEXT @RowspPage ROWS ONLY

Here's the results

Answer 1

  with cte as (SELECT ROW_NUMBER() over(PARTITION BY sdi.ID order by sdi.ID) as rn,totalCount = COUNT(*) OVER(), mailbox.ID as mailboxID, 
          sdi.ID as sdiID
           FROM [SDI].dbo.UserDocumentLocationOutbox mailbox 
           INNER JOIN [SDI].dbo.SDITransaction
           sdi on mailbox.SDITransactionID=sdi.ID 
         INNER JOIN [SYSDB].dbo.DocumentType doc on sdi.DocumentTypeID=doc.ID 
    where mailbox.CommunityID = '9ff10c7a-37f5-4580-9163-6ada55194ca7' 
    and mailbox.UserProfileID = 'f9791614-8cc0-42e3-87d1-53709bc1e099' 
    and doc.CommunityID = '9ff10c7a-37f5-4580-9163-6ada55194ca7' 
    and doc.Active=1 and doc.HideInMailbox=0  
    order by sdi.ProcessedDateTime desc 
OFFSET ((@PageNumber - 1) * @RowspPage) ROWS FETCH NEXT @RowspPage ROWS ONLY)
select totalCount, mailboxID, sdiID
from cte 
where rn < 2

Answer 2

You'll always get 4 results for this query as you have 4 different mailboxID values. I.E each of your sdiIDs has two MailboxIDs associated, so they are distinct records. You could remove one of these IDs for each by selecting the MAX(mailbox.id) as mailboxID or MIN(Mailbox.id) as mailboxID and group by ID?