Please help!



  • Hi!



    I'll tell about my problem now.

    This is a part from my table, but actually it has about 2500000 rows

    fkcreditcodeprefix Code id isadministrative MeTable

    4 PLUS-01203765 1 1 0

    4 PLUS-01217095 2 1 0

    4 PLUS-01007050 3 1 1

    4 PLUS-01217644 4 1 0

    4 PLUS-01203773 5 1 0

    4 PLUS-01078620 6 1 1

    4 PLUS-01212765 7 1 0

    4 PLUS-01217137 8 1 0

    4 PLUS-01218282 9 1 0

    4 PLUS-01217627 10 1 1

    4 PLUS-01218258 11 1 0

    4 PLUS-01212628 12 1 1



    I need to count the rows between every two ones in the column MeTable. The result have to be a column with numbers. In this case must look like:

    0

    2

    2

    3

    1



    I tried to use while loop or cursor but I had no success. Can you help me with this case, it is very imortant to me!



    Thanks



  • [Sorry about previous deleted attempt, Forum Software really is TRWTF]

    I'm probably doing homework, god help me, but the below (untested) should more or less work:

    select
     T.id -
     (
     select isnull(max(id), 0)
     from YourTable T1
     where T1.id < T.id
      and T1.v = 1
     ) -1
    from YourTable T
    where t.v =1

    EDIT: (assumes id column is unique consecutive numbers, but if not adapt accordingly)



  • Yes, that's right!!! Thank you, thank you, thank you so much :-)



  • @Zagyg said:

    I'm probably doing homework,
     

    @Rositsa said:

    Yes, that's right!!!

     


Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.