SQLTeam.com | Weblogs | Forums

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


#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


#2

Duplicate of this