score:3

Okay - contrary to what I thought, you can do this with a single recursive CTE.
Not that it's pretty...

Please note that all testing was done on DB2, which does not have a way to return arrays in-row. I don't know if SQL Server can (so you may have to parse results). I do NOT recommend attempting to do this with Linq - if anything, this should be run on the database directly.

WITH Mappings(beginId, storedId, groupId, storedPath, groupPath) as (
     SELECT a.StoredMessageId, a.StoredMessageId, a.MessageGroupId,
            a.StoredMessageId + "|", a.MessageGroupId + "|"
     FROM MessageGrouping as a
     WHERE NOT EXISTS (SELECT 1
                       FROM MessageGrouping as b
                       WHERE b.StoredMessageId < a.StoredMessageId
                       AND b.MessageGroupId = a.MessageGroupId)
     AND NOT EXISTS (SELECT 1
                     FROM MessageGrouping as b
                     WHERE b.MessageGroupId < a.MessageGroupId
                     AND b.StoredMessageId = a.StoredMessageId)
     UNION ALL
     SELECT a.beginId, a.storedId, b.MessageGroupId,
            a.storedPath, a.groupPath + b.MessageGroupId + "|"
     FROM Mappings as a
     JOIN MessageGrouping as b
     ON b.StoredMessageId = a.storedId
     AND b.MessageGroupId <> a.groupId
     AND a.groupPath NOT LIKE "%" + b.MessageGroupId + "%"
     UNION ALL
     SELECT a.beginId, a.StoredMessageId, a.groupId,
            a.storedPath + b.StoredMessageId + "|", a.groupPath
     FROM Mappings as a
     JOIN MessageGrouping as b
     ON b.MessageGroupId = a.groupId
     AND b.StoredMessageId <> a.storedId
     AND a.storedPath NOT LIKE "%" + b.StoredMessageId + "%"),

     Result_Rows (ids, num) as (
     SELECT storedPath, 
            ROW_NUMBER() OVER(PARTITION BY beginId
                              ORDER BY LENGTH(storedPath) DESC)
     FROM Mappings)
SELECT ids
FROM Result_Rows
WHERE num = 1

Yields ids of:

1|2|6|
3|7|  
4|                                

Related Articles