# SQL for Recursive Generation of Item Sequences

Oracle introduced recursive subquery factoring in version 11.2 of its database software. This offered a new way to write hierarchical queries, in addition to its Connect By clause, for navigating tree structures, such as an HR employee management hierarchy. It also offered the possibility to solve a wider range of problems using its more powerful form of recursion, including combinatorial optimization problems such as bin fitting (or knapsack) problems. I used the new technique to solve a number of such problems on this blog, starting in January 2013 with A Simple SQL Solution for the Knapsack Problem (SKP-1).

In this article, I focus on how the technique may be used to generate the sequences of items that form the basis of the solutions for the larger problems. In general we assume that we have a set of n items with unique identifiers, from which we want to form sequences of r items. Here are some definitions from Wikipedia to help us classify the different types of sequence possible:

Set (mathematics)

In mathematics, a set is a well-defined collection of distinct objects, considered as an object in its own right

Multiset

In mathematics, a multiset (aka bag or mset) is a modification of the concept of a set that, unlike a set, allows for multiple instances for each of its elements

Permutation

In mathematics, a permutation of a set is, loosely speaking, an arrangement of its members into a sequence or linear order

Combination

In mathematics, a combination is a selection of items from a collection, such that (unlike permutations) the order of selection does not matter

We can look for all sequences of r items from n, of four different types, based on the concepts above of:

1. Set/Permutation – items may not repeat and order matters
2. Set/Combination – items may not repeat and order does not matter
3. Multiset/Permutation – items may repeat and order matters
4. Multiset/Combination – items may repeat and order does not matter

## Set/Permutation – items may not repeat and order matters

Suppose we have SP(r-1), the set of all (r-1)-tuples of type Set/Permutation, we can generate SP(r) by forming a set of new r-tuples from each (r-1)-tuple t(r-1)(j):

For each item i, add i on to t(r-1)(j):

t(r)(ij) = (t(r-1)(j), i)

## Multiset/Combination – items may repeat and order does not matter

First we define the different types of sequences that are possible.

Initially we want to generate a set of all sequences satisfying certain generic conditions.