开发者

Select unique pairs in self join

开发者 https://www.devze.com 2023-04-05 09:33 出处:网络
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 i

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.

0

精彩评论

暂无评论...
验证码 换一张
取 消