How to make sql selecting loop (simple recursive query)

Question

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!

Is that sample table data, or the expected result? We need both! Also show us your current query attempt! — jarlh
– jarlh, Commented Oct 12, 2016 at 7:44
Expected output is must here..One can help better if he knows whats the expected output — XING
– XING, Commented Oct 12, 2016 at 7:55
Can't you just write SELECT * FROM versions_table WHERE NEW_VERSION <= 11.111 ... — Jens
– Jens, Commented Oct 12, 2016 at 8:07
@Jens No, because there may be other lower versions which I don't need. In output should be strict history — ILLIA N.
– ILLIA N., Commented Oct 12, 2016 at 8:11
How do you determine if there's an older version you don't need? OLD_VERSION & NEW_VERSION don't match up? — Jens
– Jens, Commented Oct 12, 2016 at 8:13

user330315 · Accepted Answer · 2016-10-12 08:20:23Z

3

This is a (simple) recursive query:

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

answered Oct 12, 2016 at 8:20

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

ILLIA N. Over a year ago

Thank you, it is what I need.

Collectives™ on Stack Overflow

How to make sql selecting loop (simple recursive query)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related