Ordering concern - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Ordering concern

Author

Topic

SebJ
Starting Member

11 Posts

Posted - 2011-10-07 : 04:56:44

Hi,
I've got a slight problem creating a table with the following pattern:

I have a column ID which is not necessarily static ascending. The 'nextID' column specifies the row to be followed.

ID | nextID | someValues...
12 | 13 | ...
13 | 14 | ...
14 | 34 | ...
15 | 45 | ...
16 | 0 | ...
34 | 15 | ...
45 | 16 | ...

According to the example above, I would like to get the order 12-13-14-34-15-45-16. What I want to achieve is to order these rows with all their appending values. the result should be:

ID | nextID | someValues...
12 | 13 | ...
13 | 14 | ...
14 | 34 | ...
34 | 15 | ...
15 | 45 | ...
45 | 16 | ...
16 | 0 | ...

Thank you for any help! When it comes to problems of this kind, the forum is amazing by the way. ;)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-07 : 04:59:49

you need to use a recursive cte for this. have a look at link below

http://msdn.microsoft.com/en-us/library/ms186243.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-07 : 05:01:36

Where do you start?
;with cte as
(
select i = min(id), seq=1 from tbl
union all
select i=nextID, seq=seq+1 from tbl t join cte on t.id = cte.i and cte.i <> 0
)
select * from cte order by seq

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-07 : 05:48:22

[code]
declare @table table
(
ID int,
nextID int
)
insert @table
select 12,13 union all
select 13,14 union all
select 14,34 union all
select 15 , 45 union all
select 16, 0 union all
select 34,15 union all
select 45 , 16 union all
select 3,20 union all
select 56,17 union all
select 20,56 union all
select 65,30 union all
select 77,38 union all
select 30,77 union all
select 38,19

;with cte as
(
select t.id,t.nextid,1 as [Level],
cast(t.id as varchar(max)) as [path]
from @table t
left join @table t1
on t1.nextid = t.id
where t1.id is null
union all
select t.id,t.nextid,c.[Level] + 1,
cast(c.[Path] + ',' + cast(t.id as varchar(1000)) as varchar(max))
from @table t
join cte c
on t.id = c.nextid
)
select id,nextid
from cte
order by left([path]+',',charindex(',',[path]+',')-1)*1,
[Level]

output
---------------------------------------------
id nextid
3 20
20 56
56 17
12 13
13 14
14 34
34 15
15 45
45 16
16 0
65 30
30 77
77 38
38 19

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SebJ
Starting Member

11 Posts

Posted - 2011-10-07 : 08:09:47

It all works, thanks to you guys!

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-10-07 : 09:33:46

If there is only one linked list, as per the original data, then recursion is not necessary and something like the following may work better with large amounts of data:


--- *** Test Data ***
CREATE TABLE #t
(
	ID int NOT NULL
	,nextID int NOT NULL
);
INSERT INTO #t
SELECT 12,13
UNION ALL SELECT 13, 14
UNION ALL  SELECT 14, 34
UNION ALL  SELECT 15, 45
UNION ALL  SELECT 16, 0
UNION ALL  SELECT 34, 15
UNION ALL  SELECT 45, 16;
--- *** End Test Data ***

WITH OrderUp
AS
(
	SELECT 2 * (ROW_NUMBER() OVER (ORDER BY ID)) AS RowNum
		,ID, nextID
	FROM #t
	WHERE ID < nextID OR nextID = 0
)
, OrderAll
AS
(
	SELECT RowNum, ID, nextID
	FROM OrderUp
	UNION ALL
	SELECT U.RowNum + 1
		,T.ID, T.nextID
	FROM #t T
		JOIN OrderUp U 
			ON T.ID = U.nextID
	WHERE NOT (T.ID < T.nextID OR T.nextID = 0)
)
SELECT ID, nextID
FROM OrderAll
ORDER BY RowNum;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-07 : 12:05:48

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources