1

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. enter image description here

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)
9
  • 1
    I'm struggling to understand what determines the uniqueness in the UNIQUE table, the second row, PETER, and the fifth row, SPIDERMAN, both have 642, 1 for ID1 and ID2 respectively. As does the last row in the DUPLICATE table, how does that work, is the name involved somehow? There's only four unique combinations of ID1 and ID2 when you exclude the transposed versions: 235, 6 324, 2 642, 1 753, 2
    – M Bain
    Jan 26, 2022 at 0:14
  • Sorry, you are right. I missed removing those from the example. No the name has no bearing here. I have updated the question.
    – GeorgeC
    Jan 26, 2022 at 3:30
  • Cool! we're on the same page, see answer below :)
    – M Bain
    Jan 26, 2022 at 4:14
  • 1
    See on lines 4 of the code you posted, try changing ID_A>ID_B to r_FID1>r_FID2, and for line 8 r_FID2>r_FID1. The field names don't have the alias until after the query runs
    – M Bain
    Jan 26, 2022 at 5:47
  • 1
    Great. I would expect that query to give you the unique records, which you could load into Qgis and save as a table. If you change the last line to include "r_UID NOT IN(SELECT" etc that should give the duplicates. Combined number of rows in the two queries should equal total rows in source table (assuming there are no NULLS to confuse things). Not sure about the last bit "I need the unique to NOT include one of the duplicates"
    – M Bain
    Jan 26, 2022 at 8:10

1 Answer 1

2

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)
3
  • Thanks unfortunately we can't use the logic that ID1 is greater than ID2. The real values are text d2f1ec17-a1d1-47e3-a587-b7482c6a2649 and 0000514c-4400-0400-0000-0000049314d9 for example. I just put the numbers into to make the example clear. Sorry.
    – GeorgeC
    Jan 26, 2022 at 4:47
  • Ah. You can still compare TEXT with a "greater than", I think, not sure about UUIDs, give it a try.
    – M Bain
    Jan 26, 2022 at 5:04
  • I tried and nothing happens when I try to execute the modified code. I have added it to the question.
    – GeorgeC
    Jan 26, 2022 at 5:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.