I have a unique scenario for which I would like to build a SQL to get desired result. Could you please help me find a SQL solution for this.
Here is how my data looks. here are 2 sets of records ..
I'm trying to get the result like below ..
111 555 -- Latest record for that group
Any help with SQL would be greatly appreciated. Thank you.
Start with the last record per group:
SELECT Col1, Col2, Col2 AS lastCol2
FROM tab AS t1
WHERE NOT EXISTS
SELECT * tab AS t2
WHERE t1.Col2 = t2.Col1
and then go up the hiearchy.
I don't know how to solve your problem in only one step, but you can use this little process
create multiset volatile table mytable (
)primary index( col1)
on commit preserve rows;
insert into mytable values(111 ,222);
insert into mytable values(222 , 333);
insert into mytable values(333 , 444);
insert into mytable values(444 , 555);
insert into mytable values(555 , 555);
insert into mytable values(100 , 200);
insert into mytable values(200 , 300);
insert into mytable values(300 , 400);
insert into mytable values(400 , 400);
REPLACE RECURSIVE VIEW ALL_START_AND_END
(Origin, Destination, Depth, Grupo)
select A.col1 as Origin, A.col2 as Destination, 0 as Depth, row_number() over( order by A.col1, A.col2 ) as Grupo
from mytable A
where not exists
( select null
from mytable B
where B.col2 = A.col1 )
select mytable.col1, mytable.col2, Depth+1, ALL_START_AND_END.Grupo
inner join mytable
on ALL_START_AND_END.Destination = mytable.col1
and ALL_START_AND_END.Depth <= 10
drop table Grupo;
create multiset volatile table Grupo
Select Origin, Grupo, Destination, min(Depth) as Depth
group by 1, 2, 3 ) as TMP
qualify rank() over ( partition by grupo
order by Depth desc ) = 1
primary index( grupo )
on commit preserve rows;
Select BASE.Origin, BASE.Grupo, BASE.Destination, BASE.Depth, GR.Destination as EndDestination, GR.Depth as EndDepth
from ALL_START_AND_END BASE
inner join Grupo GR
on BASE.Grupo = GR.Grupo
and BASE.Depth <= GR.Depth --Eliminate No Sense BASE.Depth
order by 2, 1, 3, 4
Keep in mind the where condition Depth <= 10 in line 34 to avoid an infinite loop. Maybe you should change this value to reach the end of your group.
Finding the last row of a group is simple, no need for NOT EXISTS, it's WHERE col1 = col2.
WITH RECURSIVE cte
(col1, col2, last_col2)
SELECT col1, col2, col2 AS last_col2
WHERE col1 = col2 -- start with the last row of a group
SELECT t.col1, t.col2, cte.last_col2
INNER JOIN mytable AS t
ON cte.col1 = t.col2 -- go up in the hierarchy
WHERE t.col1 <> t.col2
SELECT * FROM cte
ORDER BY 3,1
Of course this only works if there are no loops in your data...