How to create Row Count on a table which already has records in it?

Question

I have a table with 5000 rows already populated in a table.

I have a column called SEQN.

I would like to populate this column with a Row Count.

I am using:

Microsoft SQL Server Management Studio 9.00.4035.00 Microsoft Analysis Services Client Tools 2005.090.4035.00 Microsoft Data Access Components (MDAC) 6.1.7601.17514 Microsoft MSXML 3.0 4.0 5.0 6.0 Microsoft Internet Explorer 9.0.8112.16421 Microsoft .NET Framework 2.0.50727.5466 Operating System 6.1.7601

Thank you in advanced for your assistance.

SQLNewbie

Do you have any preference for an order or will any distribution of 1 through 5000 suffice? — HABO, Jun 03 '13 at 14:43
on second thought...Order By Timestamp would work out well...Thanks — scottgobucks, Jun 03 '13 at 15:43
If that meets your needs, you could accept the answer. See [How does accepting an answer work?](http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work) — HABO, Jun 03 '13 at 19:35

HABO · Answer 1 · 2013-06-03T16:46:50.103

declare @Foo as Table ( FooId Int Identity, Sequence Int, Timestamp DateTime );
insert into @Foo ( Sequence, Timestamp ) values
  ( 42, '20010203 10:18:05' ), ( 18, '20100508 22:18:05' ), ( NULL, '19960316 19:00:00' );

select * from @Foo order by Sequence;

update Foo
  set Sequence = S
  from ( select Sequence, Row_Number() over ( order by Timestamp ) as S from @Foo ) as Foo;

select * from @Foo order by Sequence;

update Foo
  set Sequence = S
  from ( select Sequence, Row_Number() over ( order by Timestamp desc ) as S from @Foo ) as Foo;

select * from @Foo order by Sequence;

How to create Row Count on a table which already has records in it?

1 Answers1