Post reply

Counting Rows in a group

  • November 3, 2005 at 1:53 am

    #89819

    hi

    i have a table that looks somthing like this:

    column A

    im trying to create a select query that will

  • November 3, 2005 at 2:00 am

    #602053

    sorry - pressed 'post reply' by mistake

    any how....

    i have a table that looks somthing like this:

      Father          Son       

        AA              11

        AA              43

        AA              11

        AA              11  

        BB              22

        BB              11 

        BB              22

    as seen the 2 columns are not unique

    THE PROBLEM:

    i need to add another column:  a Son counter

    ( gives a place for every son in his father )

    so the three columns become unique:

    Father            Son       SonNumber  

        AA              11            1

        AA              43            2

        AA              11            3

        AA              11            4

        BB              22            1

        BB              11            2

        BB              22            3

    does anybody know how to do this ????

  • November 4, 2005 at 12:09 am

    #602288

    CREATE TABLE #Family (ID int identity, Father char(2),Son int)

    INSERT INTO #Family (Father,Son)

    SELECT 'AA',11 UNION ALL

    SELECT 'AA',43 UNION ALL

    SELECT 'AA',11 UNION ALL

    SELECT 'AA',11 UNION ALL

    SELECT 'BB',22 UNION ALL

    SELECT 'BB',11 UNION ALL

    SELECT 'BB',22

    SELECT * FROM #Family

    SELECT ID, Father, Son

     , SonNumber = (SELECT COUNT(*) FROM #Family S

      WHERE S.Father = F.Father AND S.ID < F.ID)+1

    FROM #Family F

    ORDER BY ID, SonNumber

    DROP TABLE #Family

    Andy

  • November 6, 2005 at 1:58 am

    #602538

    that was exactly what i needed

    thx david

     🙂

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

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