Post reply

Update sortorder column to +1

  • August 2, 2012 at 8:52 am

    #1520896

    Hey Guys,

    Below is the code that I changed for partition purpose. I know simple sql like select and group by etc.. but this is new to me and I really appreciate that you guys are very active helping peers. I don't want to be spoonfed 🙂 so I googled the syntax and changed the query to include partition and also pull only records where isactive =1. The reason for pulling only isactive = 1 is we don't care about the isactive = 0 (from the business perspective)

    begin transaction

    select * from dbo.customerconditions;

    ;WITH ActiveUpdates AS (

    SELECT [ID], SortOrder, isActive, ROW_NUMBER() OVER( PARTITION by [Type] ORDER BY SortOrder desc ) - 1 AS rn

    FROM dbo.CustomerConditions where isactive = 1

    )

    UPDATE ActiveUpdates

    SET SortOrder = rn

    WHERE isActive = 1

    AND SortOrder IS NULL;

    select * from dbo.customerconditions;

    rollback transaction;

    In the query if I don't add "where isactive = 1" then it skips numbers because it pulls isactive = 0 and hence the increse the rownum so if one of the record is inactive and has sortorder = 5 and then the next record is active but sortorder is null then it assigns 6 to the active record. We would like it to have sortorder 5 for the active record.

    It seems like it's working. I will run the query and chage the database. I will let you know if I run across any problem.

    I really thank you guys from the bottom of my heart, cheers 🙂 🙂

  • August 2, 2012 at 9:18 am

    #1520925

    kaushal_shah26 (8/2/2012)

    I googled the syntax and changed the query to include partition and also pull only records where isactive =1. The reason for pulling only isactive = 1 is we don't care about the isactive = 0 (from the business perspective)

    I was filtering on isactive in the outer query. If you want to move that to the CTE, then you don't need the condition in the outer query.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

Viewing 2 posts - 16 through 16 (of 16 total)

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