Multiple sequences in a single table

  • 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

  • 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.

  • Thanks

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

     

    Lance Harra

  • 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

  • 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.

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

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