auto increment - group by - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

auto increment - group by

Author

Topic

cenzor
Starting Member

1 Post

Posted - 2012-06-17 : 14:05:20

Hello All,

I would like to ask how to autoincrement integer value in a column based on the value in other column. I.e. if the value changes the incrementation should start again.

E.g. I have a table with column A and B. Column B is blank
And A has the following values:

A B
x
x
x
y
y
z
z
z
z

And I would like to update the column in the following way:

A B
x 10
x 20
x 30
y 10
y 20
z 10
z 20
z 30
z 40

Thank you!

Jan

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-06-17 : 14:38:43

Check out the ROW_NUMBER() function.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-17 : 16:49:37

[code]
UPDATE t
B = Seq * 10
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY A ORDER BY (SELECT 1)) AS Seq
FROM Table
)t
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources