Multiple sequences in a single table

Question

Post reply

Multiple sequences in a single table

Lance Harra

SSC Veteran

Points: 243
More actions
May 22, 2006 at 9:28 am

#152987

I have a table of kids and thier siblings. How can I add a column to inrement the sibling count for each child, then reset at the next child.
Example:
Childid Siblingid siblingcount
2221    3331     1
2221    3332     2
4441    5551     1
4441    5552     2
4441    5553     3
6661     7771    1
8881    9991     1
8881    9992     2
8881     9993    3
8881    9994    4

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

RyanRandall SSChampion Points: 13623 More actions · Answer 1

Here's one technique you can use...

If this isn't what you want, please explain some more.

--data

if object_id('tempdb.dbo.#t') is not null drop table #t

create table #t (Childid int, Siblingid int, siblingcount int)

insert #t (Childid, Siblingid)

select 2221, 3331

union all select 2221, 3332

union all select 4441, 5551

union all select 4441, 5552

union all select 4441, 5553

union all select 6661, 7771

union all select 8881, 9991

union all select 8881, 9992

union all select 8881, 9993

union all select 8881, 9994

create clustered index #t_clustered on #t (Childid, Siblingid)

--calculation

declare @i int, @previousChildid int

select @i = 1, @previousChildid = 1

update #t set @i = case when Childid = @previousChildid then @i + 1 else 1 end,

siblingcount = @i, @previousChildid = Childid

select * from #t

--results

Childid Siblingid siblingcount

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

2221 3331 1

2221 3332 2

4441 5551 1

4441 5552 2

4441 5553 3

6661 7771 1

8881 9991 1

8881 9992 2

8881 9993 3

8881 9994 4

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

Lance Harra SSC Veteran Points: 243 More actions · Answer 2

Thanks

I kept looking at some type of loop and I knew that would be very slow.

Lance Harra

David A. Long SSCrazy Eights Points: 8421 More actions · Answer 3

Try this:

if object_id('tempdb.dbo.#t') is not null drop table #t

create table #t (ID int identity not null, Childid int, Siblingid int)

insert #t (Childid, Siblingid)

select 2221, 3331

union all select 2221, 3332

union all select 4441, 5551

union all select 4441, 5552

union all select 4441, 5553

union all select 6661, 7771

union all select 8881, 9991

union all select 8881, 9992

union all select 8881, 9993

union all select 8881, 9994

SELECT C.ID, C.Childid, C.Siblingid

, siblingcount = (SELECT COUNT(*) FROM #t S

WHERE S.Childid = C.Childid AND S.ID < C.ID)+1

FROM #t C

ORDER BY C.ID, C.Siblingid

Andy

RyanRandall SSChampion Points: 13623 More actions · Answer 4

I should mention something about the pros and cons of the 2 methods we've got here (the one I gave, and the one David gave). The method I gave should be faster, but can do updates only (i.e. not selects) and "can't be guaranteed to be right!".

Here's a related and interesting article with these 2 and some other techniques (including, shock, horror, a cursor technique )...

http://www.sql-server-performance.com/mm_cursor_friendly_problem.asp

Incidentally, this problem is number 1 on this (very useful) "beginner FAQ"...

http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.