Automatically update sequence number column

Hello Experts,
    I need to update a sequence number column based upon another column.  In the example below column 1 will have multiple instances of the same value (in this case 100 and 101).  I need the first value (based on timestamp) to be given the value ‘0’ and subsequent rows ‘previous value +1’.  My issue is that this a rather large table with many different values in column 1 so updating the table manually is not really an option.  What I was hoping to do is have a query that walks through the table and apply the appropriate sequence numbers automatically.

Thanks in advance for any help!

table example
robthomas09Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dsackerContract ERP Admin/ConsultantCommented:
Set the column you want to sequence (in this case, Column3) to this value:

ROW_NUMBER() OVER (PARTITION BY Column1 ORDER BY Column4) - 1 AS Seq
Here is a working example based on the values you supplied above:

DECLARE @Table TABLE (
    Column1     int         NOT NULL,
    Column2     int         NOT NULL,
    Column3     int         NOT NULL,
    Column4     datetime    NOT NULL )

INSERT INTO @Table VALUES (100, 123,   0, GETDATE())
WAITFOR DELAY '00:00:00.100'
INSERT INTO @Table VALUES (101, 226,   0, GETDATE())
WAITFOR DELAY '00:00:00.100'
INSERT INTO @Table VALUES (100, 6514,  0, GETDATE())
WAITFOR DELAY '00:00:00.100'
INSERT INTO @Table VALUES (100, 65165, 0, GETDATE())
WAITFOR DELAY '00:00:00.100'
INSERT INTO @Table VALUES (101, 8598,  0, GETDATE())
WAITFOR DELAY '00:00:00.100'
INSERT INTO @Table VALUES (101, 789,   0, GETDATE())

UPDATE t
SET Column3 = Seq
FROM (SELECT *,
             ROW_NUMBER() OVER (PARTITION BY Column1 ORDER BY Column4) - 1 AS Seq
      FROM @Table) t

SELECT * FROM @Table

Open in new window

The results are:
Column1	Column2	Column3	Column4
100	123	0	2015-05-29 11:28:40.763
101	226	0	2015-05-29 11:28:40.863
100	6514	1	2015-05-29 11:28:40.963
100	65165	2	2015-05-29 11:28:41.063
101	8598	1	2015-05-29 11:28:41.167
101	789	2	2015-05-29 11:28:41.267

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.