CODEWITHSUNDEEP
sqlsql-serverdistinct
PantojaDev
PantojaDev

Reputation: 11

Using DISTINCT inner join SQL

I have two tables T1 & T2 my tables look something like this

T1 [ID, AppKey, CommenterKey, Comment, NoteTime]
T2 [ID, UserKey, Firstname, Lastname]

on T2 UserKey is correlated to CommenterKey

I would like to join these two tables while filtering the duplicate Comments on the Comments Column per each unique AppKey

Any ideas on how to make this work would be greatly appreciated.

This is the sample data:

enter image description here

The idea here is to filter the duplicate comments pertaining to a certain appkey if you look at row 11-15 in the Appkey column it is the same appkey if you look at row 11-15 it is the same comment I want to filter these comments out so the query doesn't return these duplicate rows.

Below is the query I used 

SELECT Notes.Appkey,
        Notes.CommenterKey,
        Notes.Comment,
        Notes.NoteTime,
        Users.Firstname,
        Users.Lastname
FROM tblNotes AS Notes 
inner join
tblUsers AS Users ON Commenterkey = UserKey

Upvotes: 0

Views: 82

Answers (3)

Nirmal Soni
Nirmal Soni

Reputation: 11

You can also use CTE Table. Below link is introduction about CTE Table and How to use it

https://www.essentialsql.com/introduction-common-table-expressions-ctes/

I think this does what you want,

with cte as 
(
        select notes.Appkey as appKey, notes.CommenterKey as CommenterKey, notes.Comment as Comment, notes.NoteTime as NoteTime,
             users.Firstname as Firstname, users.Lastname as Lastname,
             row_number() over (partition by users.UserKey, notes.Comment order by users.UserKey) as sequenceNo
      from tblNotes as notes inner join
           tblUsers as users 
           on notes.Commenterkey = users.UserKey
)

select * from cte where sequenceNo = 1;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Your sample data is rather hard to read. However, you can use row_number() or aggregation. I think this does what you want:

select un.*
from (select n.Appkey, n.CommenterKey, n.Comment, n.NoteTime,
             u.Firstname, u.Lastname,
             row_number() over (partition by u.UserKey, n.Comment order by u.UserKey) as seqnum
      from tblNotes n inner join
           tblUsers u 
           on n.Commenterkey = u.UserKey
     ) un
where seqnum = 1;

Upvotes: 1

Mike-314
Mike-314

Reputation: 351

Based on the sample data, something like this should work. 

select  n.NoteKey,
        n.AppKey,
        n.CommenterKey,
        n.Comment,
        u.Firstname,
        u.Lastname
from    Notes n
cross apply (
    select  AppKey,
            CommenterKey,
            Comment,
            max(NoteTime) as NoteTime
    from    Notes n2
    where   n.AppKey        = n2.AppKey
    and     n.CommenterKey  = n2.CommenterKey
    and     n.Comment       = n2.Comment
    group by 
            n2.AppKey,
            n2.CommenterKey,
            n2.Comment
) ni 
join    Users u ON u.UserKey = n.CommenterKey
where   ni.NoteTime = n.NoteTime

Your biggest issues is probably going to be performance. You may want to consider adding a duplicate flag and either checking via a trigger, or scheduled job.

Upvotes: 0

Related Questions