Post reply

Update Seq

  • September 8, 2010 at 7:57 am

    #225934

    Guys,

    I have following table in which I am trying to figure out a way to update the seq column as per the logic below by grouping empid and role.

    EMPID ROLE SEQ

    1 1 1

    1 1 2

    1 2 1

    2 1 1

    2 2 1

    2 2 2

    2 2 3

    2 2 4

    Any suggestions or inputs would help.

    Thanks

  • September 8, 2010 at 8:03 am

    #1217960

    what is the logic?

    ----------
    Ashish

  • September 8, 2010 at 8:06 am

    #1217962

    Something like

    SELECT ROW_NUMBER() OVER(PARTITION BY EMPID, ROLE ORDER BY ROLE)


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • September 8, 2010 at 8:13 am

    #1217971

    Is the table in your post the starting point of your query, or is the result.

    If it is the result, I guess you want to give some sort of sequence number to the rows, based on the combination of EMPID and ROLE.

    You can try to use the ranking functions for this, in specific the ROW_NUMBER function.

    http://msdn.microsoft.com/en-us/library/ms186734.aspx

    A select statement would be something like this:

    SELECT

    [EMPID]

    ,[ROLE]

    ,[SEQ] = ROW_NUMBER() OVER(PARTITION BY [EMPID],[ROLE] ORDER BY [EMPID],[ROLE])

    FROM MyTable

    However, to use it directly in an UPDATE statement will be difficult. For example, the first two rows have the same EMPID and ROLE, but there's no way to seperate these two rows from each other as long as SEQ does not exists.

    Also, read the following article on how to ask questions in the forums, as your question was a bit confusing.

    http://qa.sqlservercentral.com/articles/Best+Practices/61537/

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • September 8, 2010 at 8:14 am

    #1217973

    Great, I was taking so long to formulate my answer, that two people were faster than me 🙂

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

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

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