NOCOUG Second SQL Challenge

Working with the second NoCOUg SQL Challenge. In the magazine there can be found also "advice for an Oracle Beginner" articles - worth reading.




Here is my five cents to towards the problem. Another answers may be found from Iggy Fernandez blog comments.

When I found the challenge, there were already some published answers to the riddle. So I started with minimizing the starting set. Got rid of nulls in the first place. And after a while ended up with a hierarchical query. On a way I draw a Graphviz picture of the riddle data. Maybe that visualizes some paths I was trying to follow trying to figure out alternative solutions. SQL commands for creating the required data.

with aa as (
select word1, word2, word3, word2 gr
  from riddle
 where word1 is not null
), bb as (
select gr,pre,word
  from aa
unpivot (word for pre in (word1 as 1, word2 as 2, word3 as 3))
), cc as (
select gr,pre,word
     , first_value(case when pre = 3 and word != gr then gr end ignore nulls)over(partition by word) bg
     , first_value(case when pre = 1 and word != gr then gr end ignore nulls)over(partition by word) ag
     , min(pre)over(partition by word) mi
     , max(pre)over(partition by word) ma
  from bb
), dd (gr,mi,ma,pre,word,ord)as (
select gr,mi,ma,pre,word,cast(2 as varchar2(10))
  from cc
 where cc.pre=2 and cc.bg is null and cc.ag is null
 union all
select cc.gr,cc.mi,cc.ma,cc.pre,cc.word
     , dd.ord||case when cc.pre = 1 then cc.mi else cc.ma end
from dd inner join cc on cc.pre in (1,3) and dd.word = cc.gr
)
select listagg(dd.word,' ')within group(order by rpad(dd.ord,10,'2'))
  from dd
;

Update 8.3.2011
Ordering with rpad seems like so borrowed from the riddle_tree. So here is another solution that maintains the ordering number while browsing the tree.

with aa as (
select word1, word2, word3, word2 gr
  from riddle
 where word1 is not null
), bb as (
select gr,pre,word
  from aa
unpivot (word for pre in (word1 as 1, word2 as 2, word3 as 3))
), cc as (
select gr,pre,word
     , first_value(case when pre = 3 and word != gr then gr end ignore nulls)over(partition by word) bg
     , first_value(case when pre = 1 and word != gr then gr end ignore nulls)over(partition by word) ag
     , min(pre)over(partition by word) mi
     , max(pre)over(partition by word) ma
  from bb
), dd (gr,mi,ma,pre,word,nord,lv)as (
select gr,mi,ma,pre,word,2222222,1
  from cc
 where cc.pre=2 and cc.bg is null and cc.ag is null
 union all
select cc.gr,cc.mi,cc.ma,cc.pre,cc.word
     , dd.nord+(cc.pre-2)*power(10,6-dd.lv)
     , dd.lv+1
from dd inner join cc on cc.pre in (1,3) and dd.word = cc.gr
)
select listagg(dd.word,' ')within group(order by dd.nord)
  from dd 
;