Complex sorting based on next and previous records in SQL

Question

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 any letter of 1 matches any letter of 2, I want to change the ordering, so that the letter matches with the following record.
2. If no matches are found the normal ordering by letter should be done.
3. The IDs are potentially not succeeding and the records are not persé in the correct order. [SQLFiddle Demo]

[Create script and SQL Fiddle demo]

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

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

DECLARE @ParentIdentity BIGINT

INSERT Parent (number) VALUES (2)
SET @ParentIdentity = @@IDENTITY
INSERT Child (parentId, letter) VALUES (@ParentIdentity, 'C')
INSERT Child (parentId, letter) VALUES (@ParentIdentity, 'B')

INSERT Parent (number) VALUES (3)
SET @ParentIdentity = @@IDENTITY
INSERT Child (parentId, letter) VALUES (@ParentIdentity, 'D')
INSERT Child (parentId, letter) VALUES (@ParentIdentity, 'B')

INSERT Parent (number) VALUES (1)
SET @ParentIdentity = @@IDENTITY
INSERT Child (parentId, letter) VALUES (@ParentIdentity, 'C')
INSERT Child (parentId, letter) VALUES (@ParentIdentity, 'A')
GO

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 clarify what I actually want to do, here is the expected result set:

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?

Answer 1

If I understand your requirement right, You have some parts of parentId and you want each part to start with the letters those are in previous part And end with letters those are in next part, If yes try this:

;WITH t AS (
    SELECT 
        c.id, 
        c.parentId,
        c.letter,
        dt.parentSeq
    FROM 
        Child c 
        JOIN (
        SELECT 
            ci.parentId, ROW_NUMBER() OVER (ORDER BY p.number) parentSeq
        FROM 
            Child ci
            JOIN
            Parent p ON ci.parentId = p.id
        GROUP BY
            ci.parentId, p.number) dt ON c.parentId = dt.parentId
)
SELECT
    p.number,
    t.letter
FROM 
    t
    JOIN
    Parent p ON t.parentId = p.id
ORDER BY
    p.number,
    CASE WHEN t.letter IN (SELECT ti.letter FROM t ti WHERE ti.parentSeq = t.parentSeq - 1) THEN 0 
        WHEN t.letter IN (SELECT ti.letter FROM t ti WHERE ti.parentSeq = t.parentSeq + 1) THEN 2 
        ELSE 1 END,
    t.letter