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

Question

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

stsivaraj

Grasshopper

Points: 10
More actions
March 11, 2016 at 7:21 am

#104353

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
===============================================
1NULLNULLFIRST1
211SECOND2
321THIRD3
411FORTH4
541FIFTH5
6 NULLNULLSIXTH1
766SEVENTH2
876EIGTH2
9NULLNULLNINTH1
10 NULLNULLTENTH1
11NULLNULLELEVEN1
12 1111TWELE2
131211THIRTEEN3
141311FOURTEEN4
EXPECTED OUTPUT - SORT BY NAME ASC
====================================
ID PARENT ID ROOT ID NAME level
===============================================
11NULLNULLELEVEN1
12 1111TWELE2
131211THIRTEEN3
141311FOURTEEN4
1NULLNULLFIRST1
211SECOND2
321THIRD3
411FORTH4
541FIFTH5
9NULLNULLNINTH1
6 NULLNULLSIXTH1
766SEVENTH2
876EIGTH2
10 NULLNULLTENTH1

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Look at using a recursive CTE to walk through the hierarchy. If you need additional help please post the DDL (CREATE TABLE statement) for the table(s) involved, sample data (as INSERT INTO statements) and the expected results based on the sample data.

Jeff Moden SSC Guru Points: 1003875 More actions · Answer 2

stsivaraj (3/11/2016)
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
===============================================
1NULLNULLFIRST1
211SECOND2
321THIRD3
411FORTH4
541FIFTH5
6 NULLNULLSIXTH1
766SEVENTH2
876EIGTH2
9NULLNULLNINTH1
10 NULLNULLTENTH1
11NULLNULLELEVEN1
12 1111TWELE2
131211THIRTEEN3
141311FOURTEEN4
EXPECTED OUTPUT - SORT BY NAME ASC
====================================
ID PARENT ID ROOT ID NAME level
===============================================
11NULLNULLELEVEN1
12 1111TWELE2
131211THIRTEEN3
141311FOURTEEN4
1NULLNULLFIRST1
211SECOND2
321THIRD3
411FORTH4
541FIFTH5
9NULLNULLNINTH1
6 NULLNULLSIXTH1
766SEVENTH2
876EIGTH2
10 NULLNULLTENTH1

This is actually very easy to do but I don't have the time to make your data readily consumable to show you actual working code. Please see the first link under "Helpful Links" in my signature line below to help us help you. Thanks and welcome aboard.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

stsivaraj Grasshopper Points: 10 More actions · Answer 3

Thanks for your response. Actually, I got solution from my manager. Thanks for your support

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

stsivaraj (3/13/2016)
Thanks for your response. Actually, I got solution from my manager. Thanks for your support

Forum etiquette would have you post your solution for others to see as your solution may be helpful to others.

Jeff Moden SSC Guru Points: 1003875 More actions · Answer 5

stsivaraj (3/13/2016)
Thanks for your response. Actually, I got solution from my manager. Thanks for your support

I'd be very interested in seeing the code that you and your manager used. Most managers get this stuff wrong performance and utility wise.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)