2011-02-28

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
;


No comments:

Post a Comment

About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. I have received Oracle ACE nomination. My main focus is on projects involving Oracle database. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.