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开发者_JAVA百科 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!
Change !=
to <
in your WHERE clause to ensure that the lowest ID always comes first:
WHERE b1.Id < b2.Id
If you have more than 1 set of IDs with different lengths, for example, a simple comparison of IDs WHERE b1.Id < b2.Id
will not work. To get all IDs in the same working space, you could take the MD5 hash in the where clause:
WHERE MD5(b1.Id) < MD5(b2.Id)
In sqlite, the MD5 hash function may not be built-in, but in its place you can use any hash function that may be built-in or create your our user-defined hash function.
精彩评论