Complex sorting based on next and previous records

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

I use Row_Number function for this problem I hope it will help you .

SELECT Number, Letter 
FROM (
    SELECT number, letter, ROW_NUMBER()over(Partition by letter,Number order by Number) as R 
    FROM Child C 
      JOIN Parent P 
      ON P.id = C.parentId) x 
ORDER BY number, R desc

Answer 2

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

Answer 3

I am not sure if this will work for some complex real data, but you can check:

;WITH cte AS(SELECT  ci.id, ci.parentId, ci.letter, p.number, 
                     DENSE_RANK() OVER (ORDER BY p.number) rn
             FROM  Child ci
             JOIN Parent p ON ci.parentId = p.id)
SELECT t1.number, t1.letter
FROM cte t1
LEFT JOIN cte t2 ON t2.rn = t1.rn - 1 AND t1.letter = t2.letter
LEFT JOIN cte t3 ON t1.rn = t3.rn - 1 AND t1.letter = t3.letter
ORDER BY t1.number, t1.letter + t2.letter DESC, t1.letter + t3.letter, t1.letter

If this doesn't work you can switch to @shA.t`s clever ordering:

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