Forum Stats

  • 3,817,249 Users
  • 2,259,295 Discussions


Data with all the best combination



  • odie_63
    odie_63 Member Posts: 8,466 Silver Trophy
    edited Apr 1, 2016 4:02PM

    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 ;
  • odie_63
    odie_63 Member Posts: 8,466 Silver Trophy
    edited Apr 1, 2016 4:23PM

    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(
               ) 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 ;
  • mathguy
    mathguy Member Posts: 10,490 Blue Diamond
    edited Apr 1, 2016 5:20PM


    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

This discussion has been closed.