> Excellent CTE query, thanks, but one question: the query uses > group_concat() and the documentation says 'The order of the concatenated > elements is arbitrary.'
To a primitive observer any sufficiently advanced technology appears to be magic. group_concat() does not concatenate items in "arbitrary order", that would be quite impossible. The items are concatenated in the order that the rows are visited, and this order is entirely predictable. > Would it not be better if Sqlite guaranteed that group_concat() > respected the order by clause, if one exists. It cannot. ORDER BY is not a query condition, but is an OUTPUT condition. By the time the ORDER BY is is applied to the result set, the concatenation is already complete. This will not (and cannot) change. You could, of course, pre-sort the items fed into group_concat, since it *does* guarantee that items will be concatenated in order (ie, the first one presented will be first, and each subsequent item presented will be concatenated in turn to the end of the string). > I think the alternative, > if group_concat does not/will not support this, would be to create yet > another CTE to replicate group_concat() functionality that does, > although that would introduce additional complexity and it would of > course be better if the Sqlite gave guarantees about this function's > behaviour instead. There are guarantee's: it will concatenate the items presented to it side-by-each in the order of presentement.
