sqlsql-serveradhoc-queries

Get DISTINCT records on INNER JOIN SQL Server


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

enter image description here


Solution

  •   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