Select duplicates that appear in two columns using QGIS

Question

Given a table like

UID | Name       | ID1   | ID2
1   | JOHN       | 324   | 2
2   | PETER      | 642   | 1
3   | MARK       | 324   | 2
4   | DIANNA     | 753   | 2
5   | SPIDERMAN  | 642   | 1
6   | JAMIE      | 6     | 235
7   | DIANNA     | 753   | 2
8   | SPIDERMAN  |  1    | 642
9   | MARK       | 235   | 6
10  | DIANNA     | 753   | 2
11  | MARK       | 642   | 1
12  | JAMIE      | 235   | 6

I need to remove all but one of the records where ID1_ID2 are the same AND where ID2_ID1 are the same PLUS if ID2_ID1 and ID1_ID2 are the same but just in reverse order.

If we concat(ID1,ID2) and then run the Delete Duplicates tool we get all the records where ID1,ID2 are the same and the same if we concat(ID2,ID1) and then use this field.

However, I also want the records like 6 and 8 that match with 9 and 11 respectively. The name column is not relevant and will be duplicated.

The result should be two tables

UNIQUE (updated based on comment)

1   | JOHN       | 324   | 2
2   | PETER      | 642   | 1

4   | DIANNA     | 753   | 2

12  | JAMIE      | 235   | 6

DUPLICATE (updated based on comment)

6   | JAMIE      | 6     | 235
7   | DIANNA     | 753   | 2
8   | SPIDERMAN  |  1    | 642
9   | MARK       | 235   | 6
10  | DIANNA     | 753   | 2
11  | MARK       | 642   | 1

3   | MARK       | 324   | 2
5   | SPIDERMAN  | 642   | 1

SQL based answer would be ideal. It doesn't matter which record is kept in the Unique table.

Select UID, concat(ID1,',',ID2) as c, count(*)
from Table1
Group by c

Results in

UID c   count(*)
8   1,642   1
9   235,6   2
1   324,2   2
6   6,235   1
2   642,1   3
4   753,2   3

8 is duplicate of 2 and 6 is a duplicate of 9 and one of each of them need to be excluded.

NOTE the real table is like below. I have highlighted the duplicates.

See http://www.sqlfiddle.com/#!9/137b67/2/0

Thinking about it more. The duplicated lines will also have the same start/end points and be spatially the same but in reverse order. Maybe this can be used to instead delete one spatial duplicate?

===== Applying the logic from M_Bain's answer with the real data I get no value when I try

WITH Good AS
(Select r_UID, r_FID1 AS ID_A,r_FID2 AS ID_B
from TR_Simple as Table1
WHERE ID_A>ID_B
),
Fixed AS
(Select r_UID, r_FID2 AS ID_A,r_FID1 AS ID_B
from TR_Simple as Table1
WHERE ID_A>ID_B
),
Combined as
(SELECT * FROM Good
 UNION
 SELECT * FROM FixeD
 ),
 UniqueIDs AS
 (SELECT ID_A,ID_B, MIN(UID) AS UIDMin
  FROM Combined
  GROUP BY ID_A,ID_B
  )
  SELECT * FROM Table1
  WHERE UID IN (SELECT UIDMin FROM UniqueIDS)

Answer 1

Here's an SQL solution: The first thing we need to do is take care of the records where ID1 and ID2 are swapped around. Lets aim for a consistent format where ID1 is the bigger number and ID2 is the smaller number. To select the records where ID1 and ID2 are the "right" way around you could use a query like this:

SELECT UID, Name, ID1, ID2 FROM Table1 WHERE ID1 > ID2

To get the records where the IDs are the "wrong" way around use this query:

SELECT UID, Name, ID2, ID1 FROM Table1 WHERE ID2 > ID1

Notice how in the second query ID2 is the third column and ID1 is the fourth column.

You can use a UNION query to combine the results of the two queries into a single list (I took the optional step of giving the ID columns an Alias, naming them ID_A and ID_B to try to make the names less ambiguous as they contain values from both columns in the source table):

SELECT UID, Name, ID1 AS ID_A, ID2 AS ID_B FROM Table1 WHERE ID1 > ID2
UNION
SELECT UID, Name, ID2 AS ID_A, ID1 AS ID_B FROM Table1 WHERE ID2 > ID1

Now we have consistent data we can use GROUP BY to get a list of unique ID pairs, we'll also retrieve a single UID value for each pair :

        SELECT ID_A, ID_B, MIN(UID) as UIDMin
        FROM (
           SELECT UID, Name, ID1 AS ID_A, ID2 AS ID_B FROM Table1 WHERE ID1 > ID2
           UNION
           SELECT UID, Name, ID2 AS ID_A, ID1 AS ID_B FROM Table1 WHERE ID2 > ID1
            )  T  
GROUP BY ID_A, ID_B

The UIDs from the query above will be used to identify our "unique" records (its a bit arbitrary but the question said it didn't matter which record as long as there is just one). Any UID not in the query will be considered a duplicate.

So to get the unique records:

SELECT * FROM Table1 
WHERE UID IN (SELECT UIDMin FROM 
  (            SELECT ID_A, ID_B, MIN(UID) as UIDMin
            FROM (
               SELECT UID, Name, ID1 AS ID_A, ID2 AS ID_B FROM Table1 WHERE ID1 > ID2
               UNION
               SELECT UID, Name, ID2 AS ID_A, ID1 AS ID_B FROM Table1 WHERE ID2 > ID1
                )  T  
    GROUP BY ID_A, ID_B)
)

To get the duplicates change the second line of the query to: WHERE UID NOT IN (SELECT UIDMin FROM

To avoid the nested subqueries the query can be rewritten using common table expressions, where each subquery is introduced sequentially. It looks a bit more wordy but can be easier to read/write/debug: http://www.sqlfiddle.com/#!5/137b6/18

WITH Good AS
  (SELECT UID, ID1 AS ID_A,ID2 AS ID_B
   FROM Table1
   WHERE ID1>ID2
  ),
Fixed AS
  (SELECT UID, ID2 AS ID_A,ID1 AS ID_B
   FROM Table1
   WHERE ID2>ID1
  ),
Combined as
  (SELECT * FROM Good
   UNION
   SELECT * FROM Fixed
  ),
UniqueIDs AS
  (SELECT ID_A,ID_B, MIN(UID) AS UIDMin
   FROM Combined
   GROUP BY ID_A,ID_B
  )

SELECT * FROM Table1
  WHERE UID IN (SELECT UIDMin FROM UniqueIDS)

EDIT: modified code for the real table

WITH Good AS
(Select r_UID, r_FID1 AS ID_A,r_FID2 AS ID_B
from TR_Simple as Table1
WHERE r_FID1>r_FID2
),
Fixed AS
(Select r_UID, r_FID2 AS ID_A,r_FID1 AS ID_B
from TR_Simple as Table1
WHERE r_FID2>r_FID1
),
Combined as
(SELECT * FROM Good
 UNION
 SELECT * FROM FixeD
 ),
 UniqueIDs AS
 (SELECT ID_A,ID_B, MIN(r_UID) AS UIDMin
  FROM Combined
  GROUP BY ID_A,ID_B
  )
  SELECT * FROM TR_Simple
  WHERE r_UID IN (SELECT UIDMin FROM UniqueIDS)