All Forums Database
snunna 4 posts Joined 06/10
18 Apr 2016
Help with Recursive query

Hi,
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 .. 
Col1         Col2 
111          222
222          333
333          444
444          555
555          555

100          200
200          300
300          400
400          400
I'm trying to get the result like below .. 
Col1       Col2
111        555   -- Latest record for that group
222        555
333        555
444        555
555        555

100        400
200        400
300        400
400        400
Any help with SQL would be greatly appreciated. Thank you.

dnoeth 4628 posts Joined 11/04
18 Apr 2016

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.

Dieter

snunna 4 posts Joined 06/10
18 Apr 2016

Thanks dnoeth.
If you don't mind, could you please explain me how to get desired result from the query you mentioned. 

AtardecerR0j0 71 posts Joined 09/12
19 Apr 2016

Hi snunna
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 (
col1 integer,
col2 integer
)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)
AS
(
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 )
 
UNION ALL
select mytable.col1, mytable.col2, Depth+1, ALL_START_AND_END.Grupo
from ALL_START_AND_END
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
as(
select *
from (
  Select Origin, Grupo, Destination, min(Depth) as Depth
  from ALL_START_AND_END 
  group by 1, 2, 3 ) as TMP
qualify rank() over ( partition by grupo
                          order by Depth desc ) = 1
)with data
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.
Regards

Be More!!

dnoeth 4628 posts Joined 11/04
19 Apr 2016

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)
AS
 (
   SELECT col1, col2, col2 AS last_col2
   FROM mytable
   WHERE col1 = col2  -- start with the last row of a group
   UNION ALL
   SELECT t.col1, t.col2, cte.last_col2
   FROM cte
   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...

Dieter

snunna 4 posts Joined 06/10
19 Apr 2016

Thank you Dnoeth and AtardecerR0j0
Both worked and going with dnoeth's single sql solution .. thanks a lot .. 

You must sign in to leave a comment.