sequence sorting

[xponcall]

Hi all, I need the following to sort out base on the condition of the 3 & 2 col. eg:

c1 c2 c3
x y 0
x c y
x n c
x a b
x b n

c1 c2 c3
x a b
x b n
x n c
x c y
x y 0


-if 0 start, get col2 value
next line, the col3 must match
col2 previous value and so on.

 

[ESquared]

add a column, call it sequence.
update the row with 0 to have sequence 0
on a join to itself, update the next row based on

update n
set n.sequence = p.sequence + 1
from
yourtable n
inner join yourtable p on n.col2 = p.col1 and n.sequence is null and p.sequence is not null

[COLOR=black #d0d0d0]When I walk, I sometimes bump into things. I am closing my eyes so that the room will be empty.[/color]

 

[Lamprey13]

You can make use of Recursive CTEs (if you have 2005):

--Setup
DECLARE @T TABLE (c1 CHAR(1), c2 CHAR(1), c3 CHAR(1))

INSERT @T
SELECT 'x',  'y',  '0'
UNION ALL SELECT 'x',  'c',  'y'
UNION ALL SELECT 'x',  'n',  'c'
UNION ALL SELECT 'x',  'a',  'b'
UNION ALL SELECT 'x',  'b',  'n'
;

-- Declare CTE
WITH Temp
AS
(
	SELECT 
		c1, 
		c2, 
		c3,
		0 AS Level
	FROM 
		@T as T
	WHERE 
		c3 = '0'
	
	UNION ALL
	
	SELECT 
		T1.c1, 
		T1.c2, 
		T1.c3,
		Level + 1
	FROM 
		@T AS T1
	INNER JOIN
		Temp AS T2
		ON T1.c3 = T2.c2
)

-- Get results
SELECT *
FROM Temp

 

[xponcall]

Oh how mush I wish I have sql 2005. In fact, I be happy with 2000, this is an sql7.0. Interest solution Esquared. So I have to get a count and do a while loop to update the table? I will give it a shoot.

thanks

 

[ESquared]

no count is necessary. Do something that generates a rowcount, perhaps the query once if nothing else, then

WHILE @@RowCount > 0
UPDATE ...

[COLOR=black #d0d0d0]When I walk, I sometimes bump into things. I am closing my eyes so that the room will be empty.[/color]