Skip to Main Content
  • Questions

  • Display all combination of given number

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Sunny.

Asked: April 03, 2019 - 4:52 pm UTC

Last updated: April 09, 2019 - 1:32 am UTC

Version: 12c

Viewed 1000+ times

You Asked

I want to print those combination which have exact 110 sum.

Here I give some data for an example.

10
20
30
60
40
70
99
90
80

And output is
80,20,10
70,30,10

and Connor said...

SQL> create table t ( x int );

Table created.

SQL> insert into t values (10);

1 row created.

SQL> insert into t values (20);

1 row created.

SQL> insert into t values (30);

1 row created.

SQL> insert into t values (60);

1 row created.

SQL> insert into t values (40);

1 row created.

SQL> insert into t values (70);

1 row created.

SQL> insert into t values (99);

1 row created.

SQL> insert into t values (90);

1 row created.

SQL> insert into t values (80);

1 row created.

SQL>
SQL> with tt  (elem, tot , str) as
  2  (  select x  elem, x  tot , to_char(x) str
  3     from t
  4     union all
  5     select x, tot+x, str||','||x
  6     from   tt, t
  7     where  t.x > tt.elem
  8  )
  9  select str from tt
 10  where tot = 110;

STR
------------------------------
20,90
30,80
40,70
10,20,80
10,30,70
10,40,60
20,30,60

7 rows selected.


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

twins

Racer I., April 05, 2019 - 6:47 am UTC

Hi,

Ingenious :)

This may be needed if the inputs aren't unique :

with
pt as (
  select x, ROW_NUMBER() OVER (ORDER BY x) pos
  from t),
tt(elem, tot, str, pos) as (
  select x elem, x tot, to_char(x) str, pos
  from pt
  union all
  select x, tot + x, str || ',' || x, pt.pos
  from   tt, pt
  where  pt.pos > tt.pos)
select str
from tt
where tot = 110

insert into t values (20);

10,20,20,60

regards,
Connor McDonald
April 09, 2019 - 1:32 am UTC

Nice stuff

Sokrates, April 08, 2019 - 12:55 pm UTC


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.