sql - eleminating the records on cross row element -
srid interaction flag flag2 items1 items2 16222 abc b 4 1852311000001100 19836811000001100 16222 abc b 4 19836811000001100 1852311000001100 18599 abc b 4 1852311000001100 10368611000001100 18599 abc b 4 10368611000001100 1852311000001100 19518 abc 2 1592311000001100 1852311000001100 19518 abc 2 1852311000001100 1592311000001100 19518 abc 2 1852311000001100 19836811000001100 19518 abc 2 19836811000001100 1852311000001100 hi , above result set of complex query. till here able achieve.
please have on first 2 records. item1 of 1st row = item2 of 2nd row , vise versa srid.if item1 , item2 srid same in order , need 1 record itand result should be
srid interaction flag flag2 items1 items2 16222 abc b 4 1852311000001100 19836811000001100 18599 abc b 4 1852311000001100 10368611000001100 19518 abc 2 1852311000001100 1592311000001100 19518 abc 2 1852311000001100 19836811000001100
depends on rest of data like, might work....
with sourcedata ( select * ( values (16222, 'abc', 'b', 4, 1852311000001100, 19836811000001100) ,(16222, 'abc', 'b', 4, 19836811000001100, 1852311000001100) ,(18599, 'abc', 'b', 4, 1852311000001100, 10368611000001100) ,(18599, 'abc', 'b', 4, 10368611000001100, 1852311000001100) ,(19518, 'abc', 'a', 2, 1592311000001100, 1852311000001100) ,(19518, 'abc', 'a', 2, 1852311000001100, 1592311000001100) ,(19518, 'abc', 'a', 2, 1852311000001100, 19836811000001100) ,(19518, 'abc', 'a', 2, 19836811000001100, 1852311000001100)) tab(srid, interaction, flag, flag2, items1, items2) ) ,cte ( select distinct s1.* sourcedata s1 join sourcedata s2 on s1.srid=s2.srid , s1.items1=s2.items2 ) ,cte2 ( select * ,row_number() over(partition srid order srid)%2 rowid cte ) select srid ,interaction ,flag ,flag2 ,items1 ,items2 cte2 rowid=1;
Comments
Post a Comment