Complex sorting based on next and previous records in SQL

This is a follow-up question on Sorting based on next and previous records in SQL

But now it gets a little more complex, for example:

1. If the last letter of number 1 is equal to one of the letters of number 2, I want to change the ordering, so that the letter matches with the following record.
2. I also want to switch ordering if the first letter of number 1 matches with the last letter of number 2.
3. If no matches are found the normal ordering by letter should be done.
4. The id's are potentially not succeeding and the records are not persé in the correct order.

Create script and sql fiddle

http://ift.tt/1euYD8f

create table Parent (
id [bigint] IDENTITY(1,1), 
number bigint NOT NULL,
PRIMARY KEY (id)
)
GO

create table Child (
id [bigint] IDENTITY(1,1), 
parentId BIGINT, 
letter VARCHAR(1) NOT NULL,
PRIMARY KEY (id),
UNIQUE (parentId, Letter),
FOREIGN KEY (parentId) REFERENCES Parent(id)
)
GO

INSERT Parent (number) VALUES (1)
INSERT Parent (number) VALUES (2)
INSERT Parent (number) VALUES (3)

INSERT Child (parentId, letter) VALUES (1, 'C')
INSERT Child (parentId, letter) VALUES (1, 'A')
INSERT Child (parentId, letter) VALUES (3, 'C')
INSERT Child (parentId, letter) VALUES (3, 'B')
INSERT Child (parentId, letter) VALUES (5, 'D')
INSERT Child (parentId, letter) VALUES (5, 'B')

Current query

Currently I am sorting with this query:

;WITH CTE AS 
(
SELECT id,ParentID,letter,

ROW_NUMBER()OVER(ORDER BY ID) seq_id,
ROW_NUMBER()OVER(PARTITION BY parentId ORDER BY ID) first_element,
ROW_NUMBER()OVER(PARTITION BY parentId ORDER BY ID DESC) Last_element
FROM Child
), CTE2 AS 
(
SELECT c1.id,c1.parentid,c1.letter,c2.parentid as c2parentid
FROM CTE c1
INNER JOIN CTE c2
ON c1.last_element = 1
AND c2.first_element = 1
AND c1.seq_id + 1 = c2.seq_id
), CTE3 AS 
(
SELECT C.parentid,C.id
FROM CTE2
INNER JOIN child C ON CTE2.c2parentid = C.parentid
AND C.letter = CTE2.letter
)
SELECT P.number, C.letter
FROM Child C
JOIN Parent P ON C.parentId = P.id
LEFT JOIN CTE3 ON CTE3.id = C.id
ORDER BY P.number, ISNULL(CTE3.id,0) DESC, C.letter

Current result set

number               letter
-------------------- ------
1                    A
1                    C
2                    B
2                    C
3                    B
3                    D

Expected result set

To clearify what I actually want to do, here is the expected result set (with C and B of number 2 switched).

number               letter
-------------------- ------
1                    A
1                    C
2                    C 
2                    B 
3                    B
3                    D

Other requirements and question

• It has to work in SQL SERVER 2005.
• There is a scenario where 3 letters per number are used, I am happy if it just uses the best match.

Can anyone point me in the right direction on how to deal with this scenario?