Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Child and parent Relation Expand / Collapse
Author
Message
Posted Monday, August 9, 2010 10:22 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, February 21, 2013 3:14 AM
Points: 134, Visits: 425

I have a table like this
Id ParentId

1 NULL
2 NULL
3 NULL
4 1
5 1
6 2
7 2
8 3
9 3



I need the ordering in result set like this



Id ParentId

1 NULL
4 1
5 1
2 NULL
6 2
7 2
3 NULL
8 3
9 3

Please do the favour
Post #966352
Posted Tuesday, August 10, 2010 2:22 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
Sorry difficult play , but desperate to see the reply

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #966430
Posted Tuesday, August 10, 2010 6:22 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 12:51 PM
Points: 15,645, Visits: 28,025
I think this does what you need.

--test table
CREATE TABLE OrderTest
(id INT,
parentid INT NULL)

--test data
INSERT INTO OrderTest
VALUES
(1, NULL),
(2, NULL),
(3, NULL),
(4, 1),
(5, 1),
(6, 2),
(7, 2),
(8, 3),
(9, 3)

--query
SELECT *
FROM OrderTest
ORDER BY COALESCE(ParentId,Id), Id



----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #966603
Posted Tuesday, August 10, 2010 6:44 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
Grant Fritchey (8/10/2010)
I think this does what you need.

--test table
CREATE TABLE OrderTest
(id INT,
parentid INT NULL)

--test data
INSERT INTO OrderTest
VALUES
(1, NULL),
(2, NULL),
(3, NULL),
(4, 1),
(5, 1),
(6, 2),
(7, 2),
(8, 3),
(9, 3)

--query
SELECT *
FROM OrderTest
ORDER BY COALESCE(ParentId,Id), Id



Ahh grant .AWESOME play with COALESCE function.


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #966625
Posted Tuesday, August 10, 2010 6:49 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 12:51 PM
Points: 15,645, Visits: 28,025
Probably would have worked with ISNULL too, but COALESCE came into my head first.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #966630
Posted Tuesday, August 10, 2010 6:53 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
Yes its working with ISNULL

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #966642
Posted Tuesday, August 10, 2010 8:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, February 21, 2013 3:14 AM
Points: 134, Visits: 425
Thanks Bhuvanesh... I am glad to you for helping out me
Post #966773
Posted Tuesday, August 10, 2010 8:59 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, February 21, 2013 3:14 AM
Points: 134, Visits: 425
Thanks Fritchey... I am glad to you for helping out me
Post #966775
Posted Wednesday, August 11, 2010 6:13 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:28 PM
Points: 1,945, Visits: 2,900
You might want to look at the Nested Sets model for this. Life will be better.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #967890
Posted Wednesday, August 11, 2010 9:41 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
CELKO (8/11/2010)
You might want to look at the Nested Sets model for this. Life will be better.
Any example or link ?


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #967920
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse