How to Sort this Data

[Vimalsoft Pty Ltd]

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 Records ID 5 and 5 are fine but this record but

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

Thank you

Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/

[J4amieC]

Your question makes no sense whatsoever. Why should record ID 2 come above record id 1? There is nothing there that could possibly identify the fact that it should do. And besides, your order by gives prescendence to the ID column:

order by id ,refParent,description

So even if there was something (such as an ordinal column) it would be ignored. If there is a fake order that children must come in, then you will need to add a column for this and order by that column.

[Vimalsoft Pty Ltd]

The record

2 156 GunningM 0

is a Child of the Parent 0 and it has no Children. So there are other records that have REFPARENT of 0 but they have children. So if the record has no child it must come close to its parent.

Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/

[Lost User]

Your ordering is wrong, you are ordering by id first so your results are in the correct order. To get the results you want, you will need to change the "order by" settings; probably to refParent first, then by id descending.

txtspeak is the realm of 9 year old children, not developers. Christian Graus

[Chris Meech]

Can you please explain the criteria that cause the records with ID = 2 and ID = 1 to sort that way? Is it because there is no reference to that ID? Thanks.

Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]

[Vimalsoft Pty Ltd]

let me Explain again. Here is my SQL Query that Produces that table till the end. The First part that i run is this

Create table [#Nodes]
(id int IDENTITY(0,1),
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 = 10
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 =10

and it Produces the table #Nodes with the Following Records

ID NODEID PARENT DESCRIPTION REFPARENT

0 149 NULL Compulsory NULL
1 155 149 One of NULL
2 156 149 GunningM NULL
3 157 155 JonesJ NULL
4 158 149 One of NULL
5 159 158 D1127 NULL

and in the next statement i create another temp table

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

and the temp table #nodes2 will look like this

ID NODEID PARENT DESCRIPTION REFPARENT

0 149 NULL Compulsory NULL
2 156 149 GunningM 0
1 155 149 One of 0
4 158 149 One of 0
3 157 155 JonesJ 1
5 159 158 D1127 4

and i made a mistake here by posting a query without a parent Field, to modify that it looks like this

select id as [id2],NodeID,parent,Description,refParent
from #nodes2
order by nodeid,parent, refParent

Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/

[dan sh]

That way you are ordering is not correct. Change the order by clause as per your preferences.

"No matter how many fish in the sea; it will be so empty without me." - From song "Without me" by Eminem

[Jorgen Andersson]

If this had been Oracle, it would have been a walk in the park:

SELECT *
FROM nodes2
CONNECT BY PRIOR Id = RefParent
START WITH RefParent IS NULL
ORDER SIBLINGS BY Id

If you have SQLServer 2008, this query might work: (Havent tested)

WITH records AS
(
SELECT
Id,
RefParent,
CONVERT(VARCHAR(MAX), ROW_NUMBER() OVER (ORDER BY Id)) AS thePath
FROM nodes2
WHERE RefParent IS NULL
UNION ALL
SELECT
n.Id,
n.RefParent,
r.thePath + '.' + CONVERT(VARCHAR(MAX), ROW_NUMBER() OVER (ORDER BY n.Id)) AS thePath
FROM records r
JOIN nodes2 n ON n.RefParent = r.Id
)
SELECT *
FROM records
ORDER BY
thePath

There are times when I really hate Oracle, but when you're working with trees it really is SO superior to SqlServer

My postings are a natural product. The slight variations in spelling and grammar enhance their individual character and beauty and are in no way to be considered flaws or defects.

[Vimalsoft Pty Ltd]

Thank you for your Reply. Last night i slept over it and i came back with this

/****** 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