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)