How to find unique combination from join in sql server -
i have 2 tables t1 , t2 below, have 2 tables below,
id d v a 1 d1 v1 -70 2 d1 v2 70 3 d1 v3 -70 4 d2 v1 120 id d v a 1 d1 v11 -70 2 d1 v12 70 3 d1 v13 -70 4 d2 v31 -120
id d v a 1 d1 v11 -70 2 d1 v12 70 3 d1 v13 -70 4 d2 v31 -120
i need find out unique combinations of records who's amounts results in zero. following expected result set.
d v d1 v2 d1 v11 d1 v1 d1 v12 d2 v1 d2 v31
d v d1 v2 d1 v11 d1 v1 d1 v12 d2 v1 d2 v31
i have tried inner join on column d , amount using row_number() function not satisfy requirement. gives first unique combination.
,temp1 (select a.v v1 ,b.v v2,a.d d1 ,b.d d2,a.a actual ,b.a acrual t1 inner join t2 b on a.d =b.d a.actual +b.acrual = 0) select * tbl inner join (select v1,v2,d1,d2, actual,acrual, row_number() over(partition v1 order v1) r1 ,row_number() over(partition v2 order v2) r2 temp1)f on (a.v = f.v1 or a.v = f.v2) , a.d = f.d1 r1 = 1 , r2 = 1
if remove condition r1=1 , r2=1 condition , put r1=r2 wrong combination selected ( v2- v13 combination selected).
updated answer: confused example result in fiddle. said wanted unique pairs, why below appear twice?:
insert result (dossier,voucher) values('d1','y100022') insert result (dossier,voucher) values('d1','y100022')
anyway assuming want given row in table part of 1 pair e.g.
should returned query:
d1 x10002
d1 y100022
should not returned query:
d1 x10002
d1 y100033
use below query:
with uniquepairs( aid, bid, ar, br ) ( select a.id, b.id, row_number() over(partition a.dossier, a.voucher order a.dossier, a.voucher) ar, row_number() over(partition b.dossier, b.voucher order b.dossier, b.voucher) br t1 inner join t2 b on a.dossier = b.dossier , a.amount+ b.amount = 0 ) select a.dossier, a.voucher uniquepairs inner join t1 on aid = a.id ar = 1 , br = 1 union select b.dossier, b.voucher uniquepairs inner join t2 b on bid = b.id ar = 1 , br = 1
Comments
Post a Comment