Help with Recursive query

Database
N/A

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.

5 REPLIES
N/A

Re: Help with Recursive query

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.

N/A

Re: Help with Recursive query

Thanks dnoeth.

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

Teradata Employee

Re: Help with Recursive query

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

N/A

Re: Help with Recursive query

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...

N/A

Re: Help with Recursive query

Thank you Dnoeth and AtardecerR0j0

Both worked and going with dnoeth's single sql solution .. thanks a lot ..