How to Sort this Data [Resolved]

Posted by Vuyiswamb under Sql Server on 2/16/2010 | Views : 1520 | Status : [Member] [MVP] [Administrator] | Replies : 2
Good Day All

i have a Simple Query

select id,NodeID,Description,refParent from #nodes2
order by id ,refParent,description


this shows the following data


ID NODEID DESCRIPTION REFPARENT
================================================================
0 149 Compulsory NULL
1 155 One of 0
2 156 GunningM 0
3 157 JonesJ 1

4 158 One of 0
5 159 D1127 4



if you can look in this table the Bolded part is fine but this record

2 156 GunningM 0


is supposed to be under its parent which is the one with ID 0, so i want to sort it so that it can have the following results



ID NODEID DESCRIPTION REFPARENT
=======================================================
0 149 Compulsory NULL
2 156 GunningM 0
1 155 One of 0
3 157 JonesJ 1
4 158 One of 0
5 159 D1127 4


Thanks

Thank you for posting at Dotnetfunda
[Administrator]



Responses

Posted by: Abhi2434 on: 2/16/2010 [Member] [Microsoft_MVP] [MVP] Silver

Up
0
Down

Resolved
See in your query you are sorting by ID...

So by any case it will get sorted using ID.

After that if you want to sort ids which are same you go for refParent etc.

I am not sure what ordering you want. Every one of your desired output seems to be unordered.

Please explain it a bit to help you.

www.abhisheksur.com

Vuyiswamb, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vuyiswamb on: 2/17/2010 [Member] [MVP] [Administrator] NotApplicable

Up
0
Down
Good Day

I slept over this and i came back with this and it solved the Problem


/****** Object: StoredProcedure [dbo].[sp_Traverse_Tree_Special] Script Date: 02/16/2010 22:54:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



ALTER  PROCEDURE [dbo].[sp_Traverse_Tree_Special] @curr int

with recompile
AS
set nocount on
if exists (select * from [tempdb].[dbo].sysobjects where id = object_id(N'[tempdb].[dbo].[#Nodes]'))
drop table [#Nodes]
if exists (select * from [tempdb].[dbo].sysobjects where id = object_id(N'[tempdb].[dbo].[#Nodes_FINAL]'))
drop table [#Nodes_FINAL]

if exists (select * from [tempdb].[dbo].sysobjects where id = object_id(N'[tempdb].[dbo].[#SemiFinal]'))
drop table [#SemiFinal]


if exists (select * from [tempdb].[dbo].sysobjects where id = object_id(N'[tempdb].[dbo].[#Surt1]'))
drop table [#Surt1]

if exists (select * from [tempdb].[dbo].sysobjects where id = object_id(N'[tempdb].[dbo].[#Children]'))
drop table [#Children]


Create table [#Nodes]
(id int IDENTITY(0,1),
NodeID int,
parent int,
[Description] varchar(128),
refParent int
)

Create table [#Nodes_FINAL]
(id int IDENTITY(0,1),
id2 int ,
NodeID int,
parent int,
[Description] varchar(128),
refParent int
)
INSERT INTO #Nodes
(NodeID, Parent, [Description])
select distinct n.ID NodeID, n.Parent, nTyp.Descr [Description]
from tbl_node n
inner join tbl_node_type nTyp on nTyp.ID = n.Type and nTyp.Descr in ('Compulsory','One of')
where n.curr = @curr
union
select distinct n.ID NodeID, n.Parent, m.Descr [Description] --, n.Type, n.Curr, m.Descr
from tbl_node n
inner join tbl_node_type nTyp on nTyp.ID = n.Type and nTyp.Descr = 'Subject'
inner join tbl_modl m on m.id = n.modl
where n.curr = @curr

select distinct nP.id as [id], nP.NodeID, nP.parent, nP.Description, nRef.ID refParent
into #nodes2 from #Nodes nP
left outer join #Nodes nRef on nP.Parent = nRef.NodeID -- look up the reference id of the parent
order by refParent,nP.Description

WITH CTENodes AS
(
SELECT
ID,
NODEID,
PARENT,
DESCRIPTION,
REFPARENT,
CAST(ROW_NUMBER() OVER(ORDER BY id ) AS VARCHAR(MAX)) NodePath
FROM #nodes2
WHERE REFPARENT is null
UNION ALL
SELECT
c.ID,
c.NODEID,
c.PARENT,
c.DESCRIPTION,
c.REFPARENT,
NodePath + '.' + CAST(ROW_NUMBER() OVER(ORDER BY C.id ) AS VARCHAR(MAX)) NodePath
FROM CTENodes AS P
JOIN #nodes2 AS C
ON C.REFPARENT = P.id
)
SELECT ID,NODEID, PARENT, DESCRIPTION, REFPARENT
into #SemiFinal
FROM CTENodes
ORDER BY NodePath,ID,REFPARENT

--These are all Records that inherit from the Root
select distinct s1.ID,S1.nodeid,s1.parent,s1.description,s1.RefParent into #Surt1 from #SemiFinal s1
inner join #SemiFinal s2
on s1.id = s2.id
where s1.RefParent = 0
and s1.id != s1.Refparent
and s1.Parent in (select Nodeid from #semifinal where Parent is null and ID = 0)

--Find Records that are Children
select s1.ID,S1.nodeid,s1.parent,s1.description,s1.RefParent into #Children from #SemiFinal s1
where Refparent <> 0

--Get the Record that does not have Children
--select s1.ID,S1.nodeid,s1.parent,s1.description,s1.RefParent from #Surt1 s1
--where s1.id not in (select refparent from #Children)

--delete those that have Children from the #surf1 table
delete #Surt1
from #Surt1 s1
where s1.id in (select refparent from #Children)


--Then Union the Results the way you want them
--First Union the Root
insert into #Nodes_FINAL(id2,nodeid,parent,Description,refParent)
select ID , nodeid , parent, [description], RefParent from #SemiFinal
where Refparent is null

--Follow with the Children
insert into #Nodes_FINAL(id2,nodeid,parent,Description,refParent)
select ID , nodeid , parent, [description], RefParent from #Surt1

--Follow with the Normal
insert into #Nodes_FINAL(id2,nodeid,parent,Description,refParent)
select ID, nodeid , parent, [description], RefParent from #SemiFinal
where Refparent is not null
and ID not in (select ID from #Surt1)



select distinct ID, nodeid , parent, [description] from #Nodes_FINAL
order by nP.id , nodeid


Thank you

Thank you for posting at Dotnetfunda
[Administrator]

Vuyiswamb, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response