Creating a Sequential Record Number field With Aggregation

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 2000 Forums

SQL Server Development (2000)

Creating a Sequential Record Number field With Aggregation

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-08-27 : 11:27:27

Jagathesan writes "Dear SQL Gurus,

First of all I would like to thank you all for your great web site. It has been very helpful in my DB career for the past few years.

All the while I was always able to find a solution to my problems at SQL Team site, but recently I need to solve a very simple problem but it looks too impossible to do.

Here is the problem guys:

Lets Say I have the following temp table (#test):

COLUMN_1   SEQUENCE_ID
----------   -----------
A               NULL
A               NULL
B               NULL
B               NULL
B               NULL
C               NULL
C               NULL
C               NULL
C               NULL

What I want to do is to number the sequence id field
aggregated by COLUMN_1 value.

COLUMN_1   SEQUENCE_ID
----------   -----------
A               1
A               2
B               1      <-- the sequence must resets back
B               2      to 1 when COLUMN_1 value change
B               3      (btw, COLUMN_1 is ordered
C               1      alphabetically)
C               2
C               3
C               4

The catch is, I do not want to use any while statement or a cursor. I just want to update it using a single statement. Like the code below does:

-----------------------------------------------------

   [I found this code from your website a year ago:]
   [-----------------------------------------------]
   <Creating a Sequential Record Number field >
   <graz on 9/25/2000 in UPDATE >

   declare @intCounter int
   set @intCounter = 0
   update Yaks
   SET @intCounter = YakSequenceNumber = @intCounter + 1

-----------------------------------------------------

The code above is one of the best simplified code I've seen for sequence numbering. However I can't use it to solve my problem as it sequentially numbers all the sequence from 1 - 9, but I believe if you tweak the above SQL statement, you will be able to solve my problem. I think last year I did manage to tweak it but I've forgotten how I did it and could not recall it.

So if you great gurus can help me, I would really appreciate that.

Thanks in advance and best regards:
Jega"

nr
SQLTeam MVY

12543 Posts

Posted - 2002-08-27 : 12:50:29

update tbl
set @seq = seq = case when @id = id then @seq + 1 else 1 end , @id = id

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources