sqlhierarchyrecursive-queryconnect-by

SQL Recursive Queries , problems with understanding "prior" expression on connect by clause


I created a table like the following , to understand recursive queries:

enter image description here

enter image description here

For this I made an insert, to cause a loop to the query and now my table looks like the following (Athens to Vienna connection has been added):

enter image description here

Now to make an uncycled recursive query , I used the connect by function and wrote the code below:

select distinct abflug,ankunft,level from flugverbindungen
start with ABFLUG = 'Wien'
connect by nocycle prior ankunft =  abflug
order by level;

This result came out :

enter image description here

I can see that the query ran till Vienna and just ended on Pressburg. But when i change the prior expression from ankunft to abflug like this:

select distinct abflug,ankunft,level from flugverbindungen
    start with ABFLUG = 'Wien'
    connect by nocycle  ankunft = prior abflug
    order by level;

I get the following result:

enter image description here

Now Athens to Vienna has level 2 , which is very strange , because the root node should be Vienna and not Athens. I also don't understand , how Laibach to Belgrad became level 4.

In conclusion I actually don't understand what the prior expression changes on the query and what it is good for. I would really appreciate it , when you would explain the prior expression with this example. What is actually changing when i swap the side of the prior expression ?


Solution

  • As we know recursive queries are calling themselves and we always take over a parents column from the row before. With prior we are defining which column we want to take over from the row before in our recursive query. In my case I am always taking the arrivals column( ankunft) and change it to my new departure so I have to use ankunft as prior column. Otherwise the results will not be correct semantically, because we wanna simulate an airplane which is flying through the stations , beginning from a station we define (in my case Wien).

    Thanks to The Impaler for trying to help me though.