Re-Sequence out of order numbers

[scottcabral1979]

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

 

[imex]

Hi,

Try:

with CTE as
(
    select Account, SEQ, ROW_NUMBER() OVER(PARTITION BY Account ORDER BY SEQ) as RowNum
    from MyTable
)

update CTE
set SEQ = RowNum

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imoveis-venda-são_paulo-residencial-apartamento[/url]