Geek Answers Handbook

Mysql Selection of reciprocal record pairs without duplicates

I have a table with two columns:

  • person_id
  • person_id, with which the identifier of the 1st field is used.

I need to select all pairs of cooperation, it is easy, but what’s the problem: the table has data such as: 987 - 102 , 103 - 104 , 104 - 103 , 21 - 102 . As a result, with such data I should have 3 pairs of interactions 21-102 , 103-104 , 21-102 , as you can see, records 103-104 and 21-102 have the same logic, how can I avoid duplicating them. Any idea?

Thanks, and best regards. Anton.

+7
source share
2 answers

You can use MySQL LEAST() and GREATEST() together with DISTINCT :

 SELECT DISTINCT LEAST(a, b), GREATEST(a, b) FROM mytable
+10
source

If maintaining the order of the elements in each pair is not important, see the answer from eggyal. This query returns a result set that is slightly different from what you specified, it returns a pair of 102-987 instead of 987-102 . It also excludes any “duplicate” pairs that appear in the table.

If preserving the order of the elements in each pair is important, and you want to return “less - more” and not “more - less” when both of these “corresponding” pairs are real, you can use something like this:

 SELECT c.col1, c.col2 FROM mytable c LEFT JOIN mytable d ON d.col1 = c.col2 AND d.col2 = c.col1 AND d.col1 <> d.col2 WHERE (d.col1 IS NULL OR d.col1 > c.col1)

To eliminate all duplicate and match pairs, add a GROUP BY clause or the DISTINCT keyword, for example

 SELECT c.col1, c.col2 FROM mytable c LEFT JOIN mytable d ON d.col1 = c.col2 AND d.col2 = c.col1 AND d.col1 <> d.col2 WHERE (d.col1 IS NULL OR d.col1 > c.col1) GROUP BY c.col1, c.col2

NOTES:

SQL Fiddle here: http://sqlfiddle.com/#!2/1d9e7/1 and here: http://sqlfiddle.com/#!2/1d9e7/2

Comparison operators are not null; they cannot return the desired result if col1 or col2 contains NULL. (The query can be modified to handle NULL values ​​for col1 and / or col2.) As indicated, both queries will return, for example, both (1,NULL) and (NULL,1) if these are “matching” “pairs” are in the table. (It comes down to the question of whether you want to consider NULL values ​​for matching or not.)

Also note that both queries will return strings where col1=col2 .

Note that the first query does NOT delete the "duplicate" rows that exist in the table. That is, if a repeating “pair”, for example (202,101) , appears in two different rows, then both will be returned (if the query does not return at least one row with a “matching” pair: (101,202) .)

It is not clear which set of results you would like to return in these cases, so the first query shows a pattern to exclude ONLY rows (larger,smaller) when the corresponding pair (smaller,larger) is in the result set.

The second query excludes ALL duplicates and match pairs.

+3
source

More articles:


All Articles