Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2009
    Posts
    62

    Unanswered: Re-Sequence out of order numbers

    Hi,

    I have the following data in a table:

    Account SEQ
    12345 1
    12345 2
    12345 4
    12345 5
    12345 7

    I need to fix the SEQ field so that no gaps exist, like this:

    Account SEQ
    12345 1
    12345 2
    12345 3
    12345 4
    12345 5

    Is there a way to do this with T-SQL?

    thanks

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Are all account same value?

  3. #3
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Something like this:
    Code:
    UPDATE some_table
       SET seq = t.new_seq
    FROM some_table
      JOIN (SELECT account,
                   seq AS old_seq,
                   row_number () OVER (partition by account ORDER BY seq) AS new_seq 
            FROM some_table) t 
         ON t.account = some_table.account AND t.old_seq = some_table.seq;
    Assuming the combination account/seq is unique in the table
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  4. #4
    Join Date
    Sep 2009
    Posts
    62
    thanks, works great!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •