Select unique pairs in self join

Question

I'm trying to write a simple query in sqlite with a self join. I want all the pairs of IDs of the products that have the same cost, but I want unique pairs (i.e. don't list the same pair twice even in different order). Here's what I've got:

SELECT b1.Id, b2.Id FROM Basic AS b1 LEFT JOIN Basic AS b2 ON b1.cost = b2.cost WHERE b1.Id != b2.Id AND b1.Cost = 5; 

So I get something like

23 | 101 23 | 205 24 | 103 101 | 23 <-- Duplicate! 

I've tried different combinations of DISTINCT and GROUP BY but I still getting dupicate pairs:

I've tried stuff like

SELECT DISTINCT bp1.Id, bp2.Id ...

& ... = 5 GROUP BY bp1.Id, bp2.Id;

How can I get rid of duplicate pairs? Any ideas?

I will appreciate your help!

Answer 1

Change != to < in your WHERE clause to ensure that the lowest ID always comes first:

WHERE b1.Id < b2.Id