Re: Identifying unique sets of combinations

On 10-Jun-2011 21:15 , Dennis Lovelady wrote:

Chuck, somehow I had missed this. Thank you, it is very useful!

On 06-Jun-2011 08:20 , CRPence wrote:

I had started with almost the identical approach as Richard,
although intending also to identify generally when a "category
lacks" just one element to have a complete match; included in the
potential matches are those with count [number of elements in a
set] one less than another set. Although I had started with CREATE
VIEW, I changed each here in the code example below to be a CTE.
The snippets may or may not be of any value or interest to you:

Rather than the CREATE FUNCTION that was included, I would normally have tried to include the equivalent visual representation of the data as generated from a recursive CTE. Having access only to v5r3 prevents actually verifying outcomes with that versus the UDF :-( Such a CTE would require something like ROW_NUMBER to achieve. Would have looked much cleaner if all the work, the visual presentation of the sets and the selection of the sets, had all been prepared in just the list of CTEs :-)

Regards, Chuck