December 3, 2003 at 10:44 am
Hi,
I have a table with a field called intIndex that needs to be updated according to the order of the identity in the same group defined by idsParent.
For example,
idsItem idsParent intIndex
------- --------- --------
444 1
445 1
446 2
447 2
448 2
would like to have
idsItem idsParent intIndex
------- --------- --------
444 1 1
445 1 2
446 2 1
447 2 2
448 2 3
How could I do that without using a cursor?
December 3, 2003 at 12:04 pm
drop table #SourceTable
go
-- building a table to take the place of your source table
create table #SourceTable (
idsItem int not null
,idsParent int not null
,intIndex int not null default 0
)
insert into #SourceTable (idsItem, idsParent)
select 444, 1 union
select 445, 1 union
select 446, 2 union
select 447, 2 union
select 448, 2
-- need appropriate clustered index
-- Uneccessary (can update source table) if clustered index is idsParent, idsItem on source table
declare @SortedData table (
cix int not null identity(1,1) primary key
,idsItem int not null
,idsParent int not null
,intIndex int not null default 0
)
insert into @SortedData (idsItem, idsParent)
select idsItem, idsParent from #SourceTable
declare
@LastParent int
,@ChildSequence int
set @LastParent = 0
set @ChildSequence = 0
update @SortedData
set
@ChildSequence = intIndex = case when idsParent <> @LastParent then 1 else @ChildSequence+1 end
,@LastParent = idsParent
select * from @SortedData order by cix
December 3, 2003 at 12:17 pm
select
baseT.idsItem
, baseT.idsParent
, (select Count(*)
from T T1
where
baseT.idsParent = T1.idsParent
and
T1.idsItem <= baseT.idsitem
) as rank
From T baseT
* Noel
December 3, 2003 at 12:52 pm
cmore: great solution but I need something not in a sproc...
noeld: that's what I did to generate the rank but I am having a hard time using this in an UPDATE statement.
Here's what I did:
UPDATE
tblMyTable
INNER JOIN
(
SELECT
i2.idsParent,
i2.idsItem,
(
SELECT
COUNT(i1.idsItem) + 1
FROM
tblMyTable i1
WHERE
i1.idsItem < i2.idsItem AND i1.idsParent = i2.idsParent
) AS Rank
FROM
tblItem AS i2
) test
ON
tblMyTable.idsItem = test.idsItem
SET
tblMyTable.intIndex = test.Rank
Unfortunately I get the following errors:
Server: Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'INNER'.
Server: Msg 170, Level 15, State 1, Line 25
Line 25: Incorrect syntax near 'test'.
December 3, 2003 at 1:01 pm
Check your syntax
Update T Set intIndex = Q.rank
FROM
(
select baseT.idsItem
, baseT.idsParent
, (select Count(*)
from T T1
where
baseT.idsParent = T1.idsParent
and
T1.idsItem <= baseT.idsitem ) as rank
From T baseT
) Q
Where
T.idsItem = Q.idsItem
And
T.idsParent = Q.idsParent
* Noel
December 3, 2003 at 1:11 pm
noeld, it's working great!
Thank you very much.
You are indeed a valued contributor!
December 3, 2003 at 2:06 pm
Aye noel's will work better for you i'm sure.
I am in the habit of using 'in-line updates' a lot because it seems to suit my needs well (dramtically different from what you needed).
I tinkered with the OLAP cubes a from microsoft a few months ago but they blow up due to exceeding its limits heirarchically.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply