I have one big query for performance related in SQL Server 2005

Question

I have one big query for performance related in SQL Server 2005.

I have data like this

id  parentId
1   null
2   1
3   1
4   2
5   4
6   3

I want the order for the records by downline with parentId and id wise like

id  Order
1   1
2   2
4   3
5   4
3   5
4   6

I don't want to use loop, because loop is creating the problem if high number of rows. Please give me easy way to do it and hurting performance.

Updated, Please run the script below

 

create table [mytable]
(
[id] int,
[parentId] int
) 
GO


INSERT INTO [mytable] ([id],[parentId])VALUES(1,NULL)
INSERT INTO [mytable] ([id],[parentId])VALUES(2,6)
INSERT INTO [mytable] ([id],[parentId])VALUES(4,9)
INSERT INTO [mytable] ([id],[parentId])VALUES(5,4)
INSERT INTO [mytable] ([id],[parentId])VALUES(6,13)
INSERT INTO [mytable] ([id],[parentId])VALUES(7,13)
INSERT INTO [mytable] ([id],[parentId])VALUES(8,5)
INSERT INTO [mytable] ([id],[parentId])VALUES(9,1)
INSERT INTO [mytable] ([id],[parentId])VALUES(13,1)
GO


; WITH    q AS 
        ( 
        SELECT  id, parentId, CAST(id AS VARCHAR(MAX)) + '/' AS path 
        FROM    mytable 
        WHERE   parentId IS NULL 
        UNION ALL 
        SELECT  t.id, t.parentId, q.path + CAST(t.id AS VARCHAR(MAX)) + '/' 
        FROM    q 
        JOIN    mytable t 
        ON      t.parentId = q.id 
        ) 
SELECT  *, ROW_NUMBER() OVER (ORDER BY path) AS rn 
FROM    q 
ORDER BY 
        path 
GO

The result of this query
ID    ParentId  Path     rn
1   NULL    1/       1
13  1   1/13/       2
6   13  1/13/6/      3
2   6   1/13/6/2/    4
7   13  1/13/7/      5
9   1   1/9/         6
4   9   1/9/4/       7
5   4   1/9/4/5/     8
8   5   1/9/4/5/8/   9



 
  

But i want the result for rn from avove result first 1 then 1/9 then1/9/... then 1/13/ then 1/13/... .Please give me the solution for that.

I want result like

ID    ParentId  Path     rn 
1   NULL    1/       1 
13  1   1/13/       6 
6   13  1/13/6/      7 
2   6   1/13/6/2/    8
7   13  1/13/7/      9
9   1   1/9/         2 
4   9   1/9/4/       3 
5   4   1/9/4/5/     4 
8   5   1/9/4/5/8/   5 

Answer 1

WITH    q AS
        (
        SELECT  id, parentId, CAST(id AS VARCHAR(MAX)) + '/' AS path
        FROM    mytable
        WHERE   parentId IS NULL
        UNION ALL
        SELECT  t.id, t.parentId, q.path + CAST(t.id AS VARCHAR(MAX)) + '/'
        FROM    q
        JOIN    mytable t
        ON      t.parentId = q.id
        )
SELECT  *, ROW_NUMBER() OVER (ORDER BY path) AS rn
FROM    q
ORDER BY
        path

Answer 2

I can't tell exactly what your database looks like but something like this should work

Select id, [Order] From Table1 Order By Order, id

Answer 3

select id, parentid as order from table 
order by coalesce(parentid, 9999), id

Answer 4

WITH    q AS 
        ( 
        SELECT  id, parentId, CAST(id AS VARCHAR(MAX)) AS path 
        FROM    mytable 
        WHERE   parentId IS NULL 
        UNION ALL 
        SELECT  t.id, t.parentId, q.path + '/' + CAST(t.id AS VARCHAR(MAX)) 
        FROM    q 
        JOIN    mytable t 
        ON      t.parentId = q.id 
        ) 
SELECT  *, ROW_NUMBER() OVER (ORDER BY path) AS rn 
FROM    q 
ORDER BY 
        path 

In above Have one more question. you are using the order by path suppose,in the case of the records like 1/13 and 1/2 , so order by is comming 1/13 and 1/2, but i want the order 1/2 and then 1/13 , because 2 is less then 13.