parent records ordering and display child records next to it

Question

parent records ordering and display child records next to it

ramrajan

SSCommitted

Points: 1737
More actions
September 7, 2015 at 10:31 am

#311412

declare @table table (
ParentID INT,
ChildID INT,
Value float
)
INSERT INTO @table
SELECT 1,1,1.2
union
select null,1,4.8
union
select null,1,4.6
union
select 2,2,1.8
union
select null,2,1.4
union
select 3,3,1.12
union
select null, 3 , 56.7
union
select null,3,43.6
select * from @table
/*
This case ParentID - Child 1 ,1 & 2,2 and 3,3 records are called as parent
where as null , 1 is child whoose parent is 1 similarly null,2 records are child whoose parent is 2 , .....
Now my requirement is to display parent records with value ascending and display next child records to the corressponding
parent and parent records are sorted ascending
*/
--Final output shuold be
PatentID ChildID VALUE
33 1.12
null3 56.7
null3 43.6
11 1.2
null1 4.8
null1 4.6
22 1.8
null1 1.4

Viewing 3 posts - 1 through 2 (of 2 total)

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

WayneS SSC Guru Points: 95460 More actions · Answer 1

ramrajan (9/7/2015)
declare @table table (
ParentID INT,
ChildID INT,
Value float
)
INSERT INTO @table
SELECT 1,1,1.2
union
select null,1,4.8
union
select null,1,4.6
union
select 2,2,1.8
union
select null,2,1.4
union
select 3,3,1.12
union
select null, 3 , 56.7
union
select null,3,43.6
select * from @table
/*
This case ParentID - Child 1 ,1 & 2,2 and 3,3 records are called as parent
where as null , 1 is child whoose parent is 1 similarly null,2 records are child whoose parent is 2 , .....
Now my requirement is to display parent records with value ascending and display next child records to the corressponding
parent and parent records are sorted ascending
*/
--Final output shuold be
PatentID ChildID VALUE
33 1.12
null3 56.7
null3 43.6
11 1.2
null1 4.8
null1 4.6
22 1.8
null1 1.4

This does what you're looking for:

WITH cte AS

(

SELECT ChildID,

ROW_NUMBER() OVER (ORDER BY VALUE) AS RN1

FROM @table

WHERE ParentID IS NOT NULL

)

SELECT t1.*

FROM @table t1

JOIN cte t2 ON t1.ChildID = t2.ChildID

ORDER BY t2.RN1, t1.ParentID DESC, t1.Value DESC;

Results:

ParentID ChildID Value

----------- ----------- ----------------------

3 3 1.12

NULL 3 56.7

NULL 3 43.6

1 1 1.2

NULL 1 4.8

NULL 1 4.6

2 2 1.8

NULL 2 1.4

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

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

ramrajan (9/7/2015)
declare @table table (
ParentID INT,
ChildID INT,
Value float
)
INSERT INTO @table
SELECT 1,1,1.2
union
select null,1,4.8
union
select null,1,4.6
union
select 2,2,1.8
union
select null,2,1.4
union
select 3,3,1.12
union
select null, 3 , 56.7
union
select null,3,43.6
select * from @table
/*
This case ParentID - Child 1 ,1 & 2,2 and 3,3 records are called as parent
where as null , 1 is child whoose parent is 1 similarly null,2 records are child whoose parent is 2 , .....
Now my requirement is to display parent records with value ascending and display next child records to the corressponding
parent and parent records are sorted ascending
*/
--Final output shuold be
PatentID ChildID VALUE
33 1.12
null3 56.7
null3 43.6
11 1.2
null1 4.8
null1 4.6
22 1.8
null1 1.4

Not sure what this is for but that looks to have at least 1 major flaw in the idea of a parent/child table including but not limited to what a parent is and what a child is.

With that in mind, what is this for?

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