sql - Select distinct tuples in mysql -
i'm having hard time reasoning problem:
given table:
+------+------------+ | id | sibling_id | +------+------------+ | 1 | null | | 2 | 3 | | 3 | 2 | | 4 | 5 | | 5 | 4 | +------+------------+
if choose ids
(1,2,3,5)
, i'd (1,2,4)
, because 3 points 2
, , 2 points 3
, want 1 of both.
expected output:
+------+------------+ | id | sibling_id | +------+------------+ | 1 | null | | 2 | 3 | | 4 | 5 | +------+------------+
how mysql (5.7) ?
solution :
select distinct least(id, coalesce(sibling, id)) test id in (1,2,3,5);
thanks !
you have make sure, tuples same (meaning example being (2,3) , not (3,2)). can putting smaller value in front , greater value @ end of tuple. use least()
, greatest()
it.
select distinct least(id, sibling_id), greatest(id, sibling_id) table id in (1,2,3,5);
Comments
Post a Comment