MySQL. How to get rid of identical records (i,j, and j,i) query?

Question

MySQL. How to get rid of identical records (i,j, and j,i) query?

Task:
Find the pairs of PC models having similar speeds and RAM. As a result, each pair is listed only once, i.e. (i,j) but not (j,i)

Here's what I wrote:

SELECT pc1.model, pc2.model FROM pc AS pc1
INNER JOIN pc AS pc2
ON (pc1.speed = pc2.speed AND pc1.ram = pc2.ram AND pc1.model <> pc2.model);

Here is what I got:

+--------+--------+
| model | model |
+--------+--------+
| ASUS4 | Intel4 |
| Intel4 | ASUS4 |
| AMD4 | AMD3 |
| AMD3 | AMD4 |
+--------+--------+

Here's what you need to:

+--------+--------+
| model | model |
+--------+--------+
| ASUS4 | Intel4 |
| AMD4 | AMD3 |
+--------+--------+

MySQL

kaelyn52 asked July 9th 19 at 12:52

Related questions

More answers about "MySQL. How to get rid of identical records (i,j, and j,i) query?"

2 answers

Barrett.Little answered on · Accepted Answer · 2019-07-09T12:54:36.403Z

Barrett.Little answered on July 9th 19 at 12:54

Solution

Not quite this way, but in my opinion solves the problem.
The query is looking for all models that have the same values of RAM SPEED. Also, I decided to go beyond the pairs, since models can be more than two.

SELECT 
 GROUP_CONCAT(pc.`model` SEPARATOR ', ') AS 'models',
pc.`speed`,
pc.`ram`
FROM pc
GROUP BY pc.`speed`, pc.`ram`
HAVING COUNT(*) >= 2

A good alternative, thanks. - kaelyn52 commented on July 9th 19 at 12:57

kaelyn52 answered on · Accepted Answer · 2019-07-09T12:56:36.403Z

kaelyn52 answered on July 9th 19 at 12:56

Solution

SELECT pc1.model, pc2.model FROM pc AS pc1
INNER JOIN pc AS pc2 ON (pc1.speed = pc2.speed 
 AND pc1.ram = pc2.ram
 AND pc1.model < pc2.model);
/* To get rid of repetitions (i,j) and (j,i)
 it was necessary to replace pc1.model <> pc2.model
 on pc1.model < pc2.model */