Discussions
Categories
- 196.8K All Categories
- 2.2K Data
- 235 Big Data Appliance
- 1.9K Data Science
- 449.9K Databases
- 221.6K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.9K SQL & PL/SQL
- 21.3K SQL Developer
- 295.5K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.1K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 154 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 158 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 401 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 230 Portuguese
Data with all the best combination
Answers
-
For fun, the recursive subquery approach ported to XQuery :
select * from xmltable( 'declare function local:iterator ($run as xs:integer, $expr as xs:string, $list as xs:integer*) as element(r)* { for $n at $i in $list let $r := $run + $n let $e := if ($expr) then concat($expr, " + ", $n) else string($n) return if ($r <= 1100) then ( if ($r >= 900) then <r><s>{$r}</s><e>{$e}</e></r> else () ) | local:iterator($r, $e, subsequence($list, $i + 1)) else () }; (:
local:iterator(0, "", /ROW/NUMBER)' passing sys_xmlgen(sys.odcinumberlist(671,238,598,438,244,153,504,480,19,352,326,444)) columns expr varchar2(4000) path 'e' , s number path 's' ) order by s ;
-
And another brute-force attack using some helper collections :
create type numberlist is table of number; create type numberlist_array is table of numberlist; with combinations (id, vals) as ( select rownum, column_value from table( cast( powermultiset( numberlist(671,238,598,438,244,153,504,480,19,352,326,444) ) as numberlist_array ) ) ) select listagg(t.column_value, '+') within group(order by null) as expr , sum(t.column_value) as s from combinations , table(vals) t group by id having sum(t.column_value) between 900 and 1100 ;
-
Hi,
As I was thinking about this problem - if I were to code it in C, I would write it as a recursive function. For a set of one number, if it is in the desired range select it, if not don't. For a set of two or more numbers, order descending (this makes the function more efficient). Call the function twice: once for the same range, with the remaining numbers (this will give the solutions that do NOT include the largest number); and once more with the remaining numbers and the range limits decreased by the first number (to get the solutions that DO include the first number). It is for this second part of the recursive call that I would start with the largest number - this makes the remaining problem smaller.
I don't know if this can be done in SQL (although I assume it can be done in PL/SQL). Perhaps a recursive subquery can be rigged to do this. Heck, I don't even know if this is in fact your solution, or equivalent to it! I'll keep this thought for later, when I will know more.
Cheers, mathguy-ro