How to remove duplicate rows based on two columns? (For example, a set of pair values)

Question

Here is my query in SQL Server ...

SELECT hs.NAME   Highschooler, 
        hs.grade  inGrade1, 
        hs2.NAME  likes, 
        hs2.grade inGrade2 
FROM    highschooler hs 
        JOIN likes l ON hs.id = l.id1 
        JOIN highschooler hs2 ON hs2.id = l.id2

I get

    HIGHSCHOOLER   INGRADE1  LIKES      INGRADE2
1-----Cassandra        9    Gabriel         9
2-----Gabriel          9    Cassandra       9
3-----Andrew           10   Cassandra       9
4-----John             12   Haley           10
5-----Brittany         10   Kris            10
6-----Alexis           11   Kris            10
7-----Gabriel          11   Alexis          11
8-----Kyle             12   Jessica         11
9-----Austin           11   Jordan          12
10----Jessica          11   Kyle            12

I want to remove paired duplicates from the HIGHSCHOOLER and LIKES column ...

as in the above table, lines 8th and 10th have the same pair (kyle-jessica) and (Jessica-kyle) , i.e. duplicate Pair and I want to delete this duplicate

This is the link of the question I came across. https://lagunita.stanford.edu/courses/DB/SQL/SelfPaced/courseware/ch-sql/seq-exercise-sql_social_query_core/

I think you need to register to get questions.

and in the marked section the marked question

https://lagunita.stanford.edu/c4x/DB/SQL/asset/socialdata.html

Answer 1

, , , , - . :

WITH cte AS(SELECT hs.NAME Highschooler ,
                   hs.grade inGrade1 ,
                   hs2.NAME likes ,
                   hs2.grade inGrade2 ,
                   ROW_NUMBER() OVER (PARTITION BY CASE WHEN l.id1 < l.id2 THEN l.id1 
                                                        ELSE l.id2 END,
                                                   CASE WHEN l.id1 < l.id2 THEN l.id2 
                                                        ELSE l.id1 END 
                                      ORDER BY (SELECT NULL)) rn
             FROM  highschooler hs
             JOIN likes l ON hs.id = l.id1
             JOIN highschooler hs2 ON hs2.id = l.id2)
SELECT * FROM cte WHERE rn = 1

:

DECLARE @t TABLE ( id1 INT, id2 INT )

INSERT  INTO @t
VALUES  ( 1, 2 ),
        ( 2, 1 ),
        ( 1, 3 ),
        ( 5, 6 ),
        ( 6, 5 ),
        ( 7, 8 );
WITH cte AS(SELECT * ,
                   ROW_NUMBER() OVER (PARTITION BY CASE WHEN id1 < id2 THEN id1 
                                                        ELSE id2 END,
                                                   CASE WHEN id1 < id2 THEN id2 
                                                        ELSE id1 END 
                                      ORDER BY (SELECT NULL)) rn
            FROM @t)
SELECT * FROM cte WHERE rn = 1

:

id1 id2 rn
1   2   1
1   3   1
5   6   1
7   8   1

Answer 2

likes. - union all ( , , , likes(id1, id2)). , join:

SELECT hs.NAME as Highschooler, hs.grade as inGrade1, hs2.NAME as likes, hs2.grade as inGrade2 
FROM (select l.id1, l.id2
      from likes l
      where l.id1 < l.id2
      union all
      select l.id1, l.id2
      from likes l
      where l.id1 > l.id2 and
            not exists (select 1 from likes l2 where l.id2 = l2.id1 and l.id1 = l2.id2)
     ) l join
     highschooler hs
     ON hs.id = l.id1 JOIN
     highschooler hs2
     ON hs2.id = l.id2;

Answer 3

SELECT hs.NAME   Highschooler, 
        hs.grade  inGrade1, 
        hs2.NAME  likes, 
        hs2.grade inGrade2 
FROM    highschooler hs 
        JOIN likes l ON l.id1 = hs.id   + 1

Answer 4

, , ,

;with cte_likes as (
    select l.id1, l.id2
    from likes as l
    where
        not exists (select * from likes as l2 on l2.id1 = l.id2 and l2.id2 = l.id1) or
        l.id1 < l.id2
)
SELECT hs.NAME   Highschooler, 
        hs.grade  inGrade1, 
        hs2.NAME  likes, 
        hs2.grade inGrade2 
FROM    highschooler hs 
        JOIN cte_likes l ON hs.id = l.id1 
        JOIN highschooler hs2 ON hs2.id = l.id2

likes ,

SELECT hs.NAME   Highschooler, 
        hs.grade  inGrade1, 
        hs2.NAME  likes, 
        hs2.grade inGrade2 
FROM    highschooler hs 
        JOIN likes l ON hs.id = l.id1 
        JOIN highschooler hs2 ON hs2.id = l.id2
where
    l.id1 < l.id2

Answer 5

I got the answer using simple nested join joins for the question

 select 
       h1.name, 
       h1.grade, 
       h2.name, 
       h2.grade  
from 
       Likes l1, Likes l2, Highschooler h1, Highschooler h2
where 
       l1.ID1=l2.ID2 and 
       l2.ID1=l1.ID2 and 
       l1.ID1=h1.ID and 
       l1.ID2=h2.ID and 
       h1.name<h2.name