Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Combinatorial problem

Luis CabralJul 27 2010 — edited Aug 2 2010
Hi there,

Please consider the following sample table:
CREATE TABLE bags AS
SELECT ROWNUM id, rownum*10 items 
FROM all_objects 
WHERE ROWNUM <= 1000;

SELECT * from bags;

        ID      ITEMS
---------- ----------
         1         10
         2         20
         3         30
         4         40
         5         50
         6         60
         7         70
         8         80
         9         90
        10        100
        11        110
...
My problem is to find a set of bags (with any number of bags in it) whose number of items sum up to a specific value. For instance, if the target value was 100 the following combinations (bag ids) would satisfy the condition:
- 1, 9
- 1, 2, 3, 4
- 10

I just need the first set that satisfies the condition, if there is any. I am using Oracle 9i so the new recursive with clause is not an option! (if it were of any help anyway as I never used it)

If the number of bags in the set was known I could use cartesian joins however that is not the case (depending on the target value the number of bags can vary from one to hundreds, from a total of few thousands bags.)

I am planning to write some PL/SQL code using memory tables because I don't see how this can be done in SQL. Anyone has done this before? Any suggestions/advice?

Thanks
Luis

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Aug 30 2010
Added on Jul 27 2010
12 comments
2,106 views