I had originally posted this in SO but reasoned that it would be more appropriate here.
I have a view in an sqlite database such as this:
SQL Fiddle (http://www.sqlfiddle.com/#!5/ae95b/1)
This represents a list of boxes and items that go into each box. The items are always ordered by
box_start and the ranges
box_end never overlap.
As shown in the first row there's a box which can store items with codes from 1 inclusive to 3 inclusive. For example the items that would go in the box A would be 'a' and 'b'.
The box-items grouping is denoted by the coloring, rows with the same color means that those are a group.
There are also items without a designated box (box label is null), such as items 'c', 'f', 'g', 'h'.
I need to write a query, if it is possible at all, to create ad-hoc boxes for items without a box and group contiguous items into the same box as follows:
As seen in the image the item 'c' and its corresponding box were labeled with '4-4' and the previously unassigned items 'f', 'g', 'h' are now grouped under the same box labeled '8-10' which corresponds to 'min(
box_start of f,g,h)-max(
box_end of f,g,h)'
I not sure how to do this in SQLite. I thought about using somekind of recursive query with CTEs but don't have a clue how to do it.