Need SQL Statement To Increment A Column Sequen. In A Group

Question

Post reply

Need SQL Statement To Increment A Column Sequen. In A Group

B. Hill

Valued Member

Points: 50
More actions
February 25, 2010 at 10:22 am

#386397

Hello.
I have a table where I need to assign an incremental value to a column within a group of another column.
For example, in the data below, I want to assign an incrementing value to the "DeptSeq" column (as shown) based on the "Deptartment".
Any help with the query to do this would be appreciated.
Example:
Department DeptSeq
----------- --------
101 1
101 2
101 3
104 1
104 2
105 1
105 2
105 3
105 4
105 5

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

SELECT Department, ROW_NUMBER() OVER (Partition By Department Order By department) AS DeptSeq

FROM

Departments

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

B. Hill Valued Member Points: 50 More actions · Answer 2

Thanks for the response.

2 issues. First, I get the error: 'ROW_NUMBER' is not a recognized function name

second, I want to update the DeptSeq column to the incremented value. Does this query do this?

Thanks.

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 3

B. Hill (2/25/2010)
Thanks for the response.
2 issues. First, I get the error: 'ROW_NUMBER' is not a recognized function name
second, I want to update the DeptSeq column to the incremented value. Does this query do this?
Thanks.

Let me guess... either you're using SQL Server 2000 or you're running a database in the 2000 compatibility mode. Are you actually running 2005 or 2000?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

B. Hill Valued Member Points: 50 More actions · Answer 4

Good call. Running SQL Server 2005 but the database is in 2000 compatibility mode.

Ideas?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Switch it to compatibility mode 90?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

B. Hill (2/25/2010)
second, I want to update the DeptSeq column to the incremented value. Does this query do this?

No. I wrote a select for you. A select returns data, doesn't change it. You can change the query into an update statement, though I think you'll need to include the primary key on the table within the query to do so properly. You do have a primary key?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

zukko SSC Veteran Points: 227 More actions · Answer 7

your update statement would be something like (untested code):

UPDATE Departments

SET DeptSeq = cte.deptSeq

FROM (

SELECT Department, ROW_NUMBER() OVER (Partition By Department Order By department) AS DeptSeq FROM Departments

) cte WHERE Departments.Department = cte.department

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

zukko (2/25/2010)
your update statement would be something like (untested code):
UPDATE Departments
SET DeptSeq = cte.deptSeq
FROM (
SELECT Department, ROW_NUMBER() OVER (Partition By Department Order By department) AS DeptSeq FROM Departments
) cte WHERE Departments.Department = cte.department

How's SQL supposed to know which of the rows in the outer should be updated with which row from the inner? Department is NOT unique.

That's why I said that including the primary key in the query will be necessary.

Change that query to join the outer to the inner on the primary key and it'll work as expected. Otherwise, maybe, maybe not.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

zukko SSC Veteran Points: 227 More actions · Answer 9

sorry just noticed that the department is not primary 😛

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 10

B. Hill (2/25/2010)
Good call. Running SQL Server 2005 but the database is in 2000 compatibility mode.
Ideas?

Unless you can do what Gail says and run the DB in the 90 compatible mode, there are several ways to do this...

You could use a "Triangular Join" which could eat the face off your server (see the following article for why)...

http://www.sqlservercentral.com/articles/T-SQL/61539/

Obviously, you could use a (ugh!) Cursor. A good forward only, read only, static cursor will perform just as well as a well written While Loop and in SQL Server 2000, it's actually one of the faster methods you can use.

Or, you can use what is known as the "Quirky Update". There are some rules you need to follow to keep it from blowing up in your face but, if you follow those rules, it'll update a million row table in about 3 seconds. Because I want to make sure you at least know where the rules are, I'll simply point you to the article instead of just giving you the code you need...

http://www.sqlservercentral.com/articles/T-SQL/68467/

Don't let the article or the follow on discussions scare you away from using the "Quirky Update". Just, if you do, take the time to do it properly because it can bite you pretty hard if you don't.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Actually, there's one more if this is a once-off update.

Run the update in master and use 3-part naming to reference the DB in question. Compat mode is controlled by the current DB context, not the source and destination of the data

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

WayneS SSC Guru Points: 95460 More actions · Answer 12

GilaMonster (2/25/2010)
Compat mode is controlled by the current DB context, not the source and destination of the data

Okay, I made it through the day and finally learned something new! Good info Gail... THANKS!

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 13

GilaMonster (2/25/2010)
Actually, there's one more if this is a once-off update.
Run the update in master and use 3-part naming to reference the DB in question. Compat mode is controlled by the current DB context, not the source and destination of the data

Very cool. Hadn't thought of it that way but makes total sense. Thanks, Gail.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)