Complex sorting based on the following and previous records in SQL

Question

This is the next question about Sorting based on the following and previous records in SQL

But now it is becoming a little more complicated, for example:

• If any letter from 1 matches any letter 2, I want to change the order so that the letter matches the next entry.
• If no matches are found, normal lettering should be performed.
• Identifiers are potentially unsuccessful, and records are not displayed 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 request
I am currently 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
To clarify what I really 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 should work in SQL Server 2005 .
• There is a scenario that uses 3 letters per number, I am glad if it uses the best match.

Can someone point me in the right direction how to handle this scenario?

Answer 1

If I understand your requirement correctly, you have some parts of parentId , and you want each part to start with a letter , which are in the previous part, and end with a letter , which are in the 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 2

I'm 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 does not work, you can switch to smart order @ shA.t`:

 ;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 

Answer 3

I am using the Row_Number function for this problem. Hope this helps 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