Please help to form a request for the exclusion of duplicate interchangeable fields?

Question

MySQL

There is a friends table, which contains 4 columns: "№, User_ID1, User_ID2, FrDate", respectively. serial number, ID of the first and second users and the date the friendship began. It is required to exclude duplicate entries, provided that the User_ID1 and User_ID2 columns are interchangeable, i.e. User_ID1 = 1, User_ID2 = 2 and User_ID1 = 2, User_ID = 1 - are equivalent (duplicate) and you need to leave the entry that is earlier in date. I broke my whole head, tried to use cursors, it didn’t really work out.

I tried to combine a regular table and an inverted table to compare the columns, but not all values ​​are excluded either.

SELECT DISTINCT *
FROM (
    SELECT DISTINCT User_ID1, User_ID2, FrDate, N 
    FROM friends AS B GROUP BY FrDate
    UNION SELECT DISTINCT User_ID2, User_ID1, FrDate, N 
    FROM friends AS B GROUP BY FrDate
     ) AS A
GROUP BY A.User_ID2, A.User_ID1, A.FrDate

That is, from the original table:

|      User_ID1     |      User_ID2      |   FrDate  |    N   |
|        1          |          2         | 2019-01-05|    1   |
|        2          |          1         | 2020-05-12|    2   |
|        3          |          4         | 2009-11-15|    3   |
|        5          |          7         | 2007-01-01|    4   |

Should get:

|      User_ID1     |      User_ID2      |   FrDate  |
|        1          |          2         | 2019-01-05|  
|        3          |          4         | 2009-11-15|   
|        5          |          7         | 2007-01-01|

Thank you in advance!

Answer 1

Group by

CASE WHEN User_ID1 > User_ID2 THEN CONCAT ( User_ID2, '-', User_ID1 )
  ELSE CONCAT ( User_ID1, '-', User_ID2 )
END CASE