How do I make recursion in SQL only with select?

Question

Is there a way to have a recursion in Oracle SQL only using select statements?

For example how do I sum a column in a table without using sum()?

Can you give an example using a dummy dataset? Recursion is possible in Oracle using CTEs, but the table structure is needed to give you a meaningful example. Also, it's not really used to just replace SUM(), so an example data-set will give a clue as to whether this is the best approach. — MatBailie
– MatBailie, Commented Dec 7, 2011 at 11:14
I don't mean this to be the best solution, it is for educational purposes. If you need a dataset, let it be a table with a single column called VAL and the data itslelf is 1 3 56 7 98 12 — Yrogirg
– Yrogirg, Commented Dec 7, 2011 at 11:19
Which version of Oracle? Recursive CTEs only came in with 11g. — user359040
– user359040, Commented Dec 7, 2011 at 11:35
There is no way any sane person would attempt to use recursion to sum a column (except for a sub-tree of a table which has a 1:N relationship to itself). — symcbean
– symcbean, Commented Dec 7, 2011 at 12:42
In SQL, recursion is used to map relationships where the parent and child are the same table. That's not the case in your theoretical scenario, so it's not the correct tool to use for this job. The right way to do this is to use sum. I'm not sure what the point is of asking for a convoluted and inefficient solution to a problem with a known simple and efficient solution. — Allan
– Allan, Commented Dec 7, 2011 at 13:23

Community · Accepted Answer · 2017-05-23 10:34:23Z

2

The answer to this question demonstrates recursion in Oracle, for both recursive CTEs (Oracle 11 onwards) and Oracle's own CONNECT BY syntax (Oracle 10g and earlier).

edited May 23, 2017 at 10:34

CommunityBot

11 silver badge

answered Dec 7, 2011 at 12:05

user359040

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

Comments

Mark Pim · Accepted Answer · 2011-12-07 12:12:08Z

-1

create table #temp (
id int,
value int)


insert into #temp values (1, 1)
insert into #temp values (2, 2)
insert into #temp values (3, 3)

declare @sum int
declare @biggest int
select @biggest = max(id) from #temp
select @sum = 0

while @biggest != 0
begin
  select @sum = @sum + value from #temp where id = @biggest
  select @biggest = @biggest - 1
end

select @sum

edited Dec 7, 2011 at 12:12

Mark Pim

10.1k7 gold badges43 silver badges60 bronze badges

answered Dec 7, 2011 at 11:21

aF.

67k45 gold badges141 silver badges201 bronze badges

3 Comments

MatBailie Over a year ago

That's not recursion though. I've no idea why recursion specifically is required, but it's what the OP is asking for...

aF. Over a year ago

@Dems humm and the while cycle? Can it be considered recursion?

Andriy M Over a year ago

Recursion is when an entity invokes itself (usually with a different set of arguments). Loops do have something in common with recursion but, to my knowledge, they have never been considered so.

Collectives™ on Stack Overflow

How do I make recursion in SQL only with select?

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related