ILLIA N. ILLIA N. - 1 month ago 8
SQL Question

How to make sql selecting loop (simple recursive query)

I have a table, where I have columns NEW_VERSION and OLD_VERSION
OLD_VERSION also has it's older version.
I need to make a query, which will be selecting the whole history of versions

For example:
Table versions_table:

OLD_VERSION | NEW_VERSION
10.333 11.111
38.888 39.999
37.777 38.888
9.222 10.333
8.111 9.222
7.999 8.111
36.666 37.777
35.555 36.666


We know only newest version "11.111".
For query
SELECT * FROM versions_table WHERE NEW_VERSION = 11.111 ...

Output should be:

OLD_VERSION | NEW_VERSION
10.333 11.111
9.222 10.333
8.111 9.222
7.999 8.111


Could you please advise me, what is the suitable approach for this?

Thank you!

Answer

This is a (simple) recursive query:

select *
from versions
start with new_version = '11.111'
connect by prior old_version = new_version;