Using the data at the bottom of this message, the CTE based code produces the following:

------------------------------------------------------------------------------------------------

3 5 zh A

112zhA

121zhC

141enA

181enA

201enC

------------------------------------------------------------------------------------------------

This corresponds to your expected output as specified in your second post of this thread. All you have to do is remove rowKey and locale from the final select statement.

SELECT 3 as RowID, 'A' as Code UNION ALL

SELECT 11 as RowID, 'A' as Code UNION ALL

SELECT 12 as RowID, 'C' as Code UNION ALL

SELECT 14 as RowID, 'A' as Code UNION ALL

SELECT 18 as RowID, 'A' as Code UNION ALL

SELECT 20 as RowID, 'C'

I have tested it against one of the patterns you just sent and found a discrepancy there which can be fixed, but first let me ask: Will you ever have THREE matching rows in a series of A's like this:

11 2 zh A

12 2 zh A

13 2 zh A

If so, which rowID should be returned, 12 or 13 ??

-----------------------------------------------------------------------

rowID rowKey locale code

11enC

21deC

35zhA

44enA

53enA

102zhA

112zhA

121zhC

131deC

141enA

153enA

163deA

181enA

193deA

201enC

211enC