Sort based on the following and previous records in SQL

Question

I am trying to order a specific request, taking into account the next and previous records, but I cannot do this. I would like to order by number and letter, but if, for example, the last letter of number 1 is equal to one of the letters of number 2, I want to change the order so that the letter matches the next entry.

Create script and SQL fiddle demo

 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, 'A') INSERT Child (parentId, letter) VALUES (1, 'C') INSERT Child (parentId, letter) VALUES (2, 'B') INSERT Child (parentId, letter) VALUES (2, 'C') INSERT Child (parentId, letter) VALUES (3, 'B') INSERT Child (parentId, letter) VALUES (3, 'D') 

Current request

I am currently sorting with this query:

 SELECT P.number, C.letter FROM Child C JOIN Parent P ON C.parentId = P.id ORDER BY P.number, 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 (with switching C and B with number 2).

 number letter -------------------- ------ 1 A 1 C 2 C --switched 2 B --switched 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.
• I am also interested in solutions for later versions of SQL Server (for training), but they do not answer my question.

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

Answer 1

You can do something like this.

• Define the first and last letter of each parent using ROW_NUMBER() and PARTITION BY
• Match the last record of the previous id with the first record of the next id .
• Check if the second parent identifier has a letter that matches the letter selected above
• Use LEFT JOIN and use CASE or ISNULL to set a higher priority for the id entry in which the letter was mapped

Query

 ;WITH CTE AS ( SELECT id,ParentID,letter, 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.id +1 = c2.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 

Output

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

SQL Fiddle

EDIT

If your ids not sequential, you can change CTE1 and CTE2 to use ROW_NUMBER()OVER(ORDER BY ID) seq_id .

 ;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 ) 

The rest of the code remains the same.

SQL Fiddle