T SQL puzzle for Update in SQL Server 2005

Suppose we have one table with ID, ParentID and SortOrder and we want to stimulate sort order based on ParentID. How do we do that without looping and cursor? There may be quite a few ways to do so but I would like to go for simple T-SQL.

--table

Create Table SQLPuzzle

(

ChildID Int,

ParentID Int,

SortOrder Int

)

--data

INSERT INTO SQLPuzzle VALUES (1,1,5)

INSERT INTO SQLPuzzle VALUES (2,1,10)

INSERT INTO SQLPuzzle VALUES (3,2,25)

INSERT INTO SQLPuzzle VALUES (4,3,10)

INSERT INTO SQLPuzzle VALUES (5,2,40)

GO

--now I want data as output something like

--ChildID ParentID SortOrder

------------- ----------- -----------

--1 1 1

--2 1 2

--3 2 1

--5 2 2

--4 3 1

--I will create clustered index first to make data in proper order

CREATE CLUSTERED INDEX IDX_CLUST ON SQLPuzzle(ParentID asc, ChildID ASC)
GO

--after creating clustered index

--let us logically define two variable to

--check parent id and current sorting order

DECLARE @Sort int,@Parent int

SET @Sort=1

SET @Parent=-1

--asign @sort variable with current sort order

--first time @parent variable will be -1 so it will not match up with

--ParentID column so first row will get value 1

--next time when second record will come our @parent variable will have value 1

--so it will match up with ParentID so sortorder will be will be assigned by @sort+1

UPDATE SQLPuzzle

SET @Sort = SortOrder = CASE WHEN @Parent<>ParentID then 1 ELSE @Sort +1 END,

@Parent = ParentID

GO

--check whether above batch of update has worked

select * from sqlpuzzle

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com