Select Distinct Records on a Join

Select distinct records on a join

A DISTINCT should do what you're looking for:

SELECT DISTINCT items.ItemName, items.ItemId FROM items
JOIN sales ON items.ItemId = sales.ItemId 
WHERE sales.StoreID = ? ORDER BY sales.SaleWeek DESC;

That would return only distinct items.ItemName, items.ItemId tuples.

SQL Distinct value over joined tables

To provide my solution:
I ended up using a nested distinct through a join and all the unnested values (all 20+) of them had to be wrapped around a MIN(x), seeing as those values didnt matter that much, so long as only one distinct value was returned.

SELECT DISTINCT WHILE JOINING TABLES

If you want distinct values, then use SELECT DISTINCT:

SELECT DISTINCT fc.Indv_Sys_Id, fc.Dt_Sys_Id
FROM MiniHPDM..Fact_Claims fc INNER JOIN
     MiniHPDM..Dim_Date d 
     ON fc.Dt_Sys_Id = d.Dt_Sys_Id;

DISTINCT is not a function. It is a keyword, used in this case with SELECT.

Given that the join should succeed, I imagine that this returns the same results:

SELECT DISTINCT fc.Indv_Sys_Id, fc.Dt_Sys_Id
FROM MiniHPDM..Fact_Claims fc
WHERE fc.Dt_Sys_Id IS NOT NULL;

Select Distinct rows from the joining of two SQL tables

The JOIN portion is unnecessary given your example above, but in the event that you actually only want records that are included in the first table, you could do an INNER JOIN like so.

SELECT tb.UniqueID, MIN(DATE) as [Date] FROM [Table B] tb
INNER JOIN [Table_A] ta ON ta.[Unique ID] = tb.UniqueID
GROUP BY tb.UniqueID

I've used your table and column names, despite the lack of naming consistency, and the fact that your tables and columns have spaces in them. (Personally, I'd advise against this in any SQL implementation, but that's a separate issue.)

Without the join you'd just need to do...

SELECT UniqueID, MIN(DATE) as [Date] FROM [Table B]
GROUP BY UniqueID

Get DISTINCT records on INNER JOIN SQL Server

  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

---

Related Topics