SQLTeam.com | Weblogs | Forums

Parent and child relationship is broken when do the sort based on parent name

sql2008

#1

Hi All,
I want to sort the name based on 1st level (sort only applicable root id is null or level ==1). If i search based on name the result set is broken the parent and child relationship.
I am using sql server database. Please let me know what are the fesible way to sort based on 1st level. I am having 5Lakh records. Also, I am considering the performance.

ID PARENT ID ROOT ID NAME level

1 NULL NULL FIRST 1
2 1 1 SECOND 2
3 2 1 THIRD 3

4 1 1 FORTH 4
5 4 1 FIFTH 5

6 NULL NULL SIXTH 1
7 6 6 SEVENTH 2
8 7 6 EIGTH 2
9 NULL NULL NINTH 1
10 NULL NULL TENTH 1
11 NULL NULL ELEVEN 1
12 11 11 TWELE 2
13 12 11 THIRTEEN 3
14 13 11 FOURTEEN 4

EXPECTED OUTPUT - SORT BY NAME ASC

ID PARENT ID ROOT ID NAME level

11 NULL NULL ELEVEN 1
12 11 11 TWELE 2
13 12 11 THIRTEEN 3
14 13 11 FOURTEEN 4
1 NULL NULL FIRST 1
2 1 1 SECOND 2
3 2 1 THIRD 3
4 1 1 FORTH 4
5 4 1 FIFTH 5
9 NULL NULL NINTH 1
6 NULL NULL SIXTH 1
7 6 6 SEVENTH 2
8 7 6 EIGTH 2
10 NULL NULL TENTH 1


Parent and child relationship is broken when do the sort based on parent name
#2

It would be much easier to understand your question and respond if you provide data that can be copied and pasted into SSMS. The way you have posted it, with no alignment etc., it is hard to read, and even harder to understand the logic you want.

What I mean is, write something like I am showing below. I am showing only an example that you would post if you had only the very first row that you posted. Fill in the rest, and also create another table and fill in the desired output with an additional column to order it by, so one can see the sort order you want.

CREATE TABLE #test(id INT, parentid INT, rootid INT, [name] VARCHAR(32), [level] INT);
INSERT INTO #test VALUES (1,NULL,NULL, 'FIRST',1);