William William - 3 months ago 6
SQL Question

Selecting pairs of elements

Well, I have table with a list of computers, and theirs speed and RAM. How can I make a query to retrieve all the pairs of computers with the same speed and RAM as example? I can only show a pair once, so (i,j) instead of (i,j) and (j,i)

something like this:

Model | Speed | ram
1006 | 200 | 32
1007 | 200 | 32
1008 | 160 | 16
1009 | 160 | 32


So the par will be 1006 and 1007

Answer

This is a self-join:

select c.model, c2.model, c.speed, c.ram
from computers c join
     computers c2
     on c.speed = c2.speed and c.ram = c2.ram and c.model < c2.model;