Creating Aggregated Sequential Record Numbering

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

Transact-SQL (2000)

Creating Aggregated Sequential Record Numbering

Author

Topic

xega
Starting Member

10 Posts

Posted - 2002-08-22 : 18:48:53

Guys,

I think I have a rather simple problem, but I could not get it solved. Anyone out there to help me out.

OK, here is the problem. Lets say I have a temp table #temp that look like the on below:

 
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 is the #temp table to look like this:


COLUMN_1	SEQUENCE_ID
--------	-----------
A			1
A			2
B			1  <- The sequence_id needs to be reset
B			2     to 1 when the row value in COLUMN_1
B			3     changes
C			1
C			2
C			3
C			4

Seing from the above, the sequencing for SEQUENCE_ID is by COLUMN_1 aggregation.

The real catch is I do not want to use while loops or cursors, I want to update using single line update like the one below:

[I got this code off the SQL Team website]
[[url]http://www.sqlteam.com/item.asp?ItemID=765[/url]]
[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

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

However the code above only sequences in the following manner:


COLUMN_1	SEQUENCE_ID
--------	-----------
A			1
A			2
B			3  
B			4
B			5
C			6
C			7
C			8
C			9

So I need to to tweak the statement to number the sequence by
aggregation. I think I've done it some time back, but I could not recall it. So help would be really appreciated.

Thanks in advance and regard
Xega

Whenever I start to think, my brain freezes therefore I think I'm cool...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-08-22 : 20:46:38

DECLARE @col1 varchar(1), @seq int
SELECT @seq=0
UPDATE #temp
SET @seq = SEQUENCE_ID = CASE @col1 WHEN COLUMN_1 THEN @seq+1 ELSE 1 END,
@col1 = COLUMN_1

Realize that this may duplicate sequence numbers if the table is not physically ordered as you described here. You can't always guarantee that it will be, and the default behavior may change in future versions of SQL Server.

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-08-23 : 08:21:27

Nice one rob ... thought I'd get this to him using a sequence table ... without having to use local variables ...

CREATE TABLE #temp (Column_1 CHAR(1), Sequence_ID INT)

INSERT INTO #temp (Column_1) VALUES ('A')
INSERT INTO #temp (Column_1) VALUES ('A')
INSERT INTO #temp (Column_1) VALUES ('B')
INSERT INTO #temp (Column_1) VALUES ('B')
INSERT INTO #temp (Column_1) VALUES ('B')
INSERT INTO #temp (Column_1) VALUES ('C')
INSERT INTO #temp (Column_1) VALUES ('C')
INSERT INTO #temp (Column_1) VALUES ('C')
INSERT INTO #temp (Column_1) VALUES ('C')

SELECT totals.Column_1, seq.Value
FROM (SELECT Column_1, COUNT(*) AS TotalCount FROM #temp GROUP BY Column_1) AS totals
INNER JOIN Sequences AS seq ON seq.Value <= totals.TotalCount AND seq.Value > 0

DROP TABLE #temp

ha ha though I must admit your solution is twice as fast ... assuming he can keep it in order ...

Edited by - onamuji on 08/23/2002 08:39:56

xega
Starting Member

10 Posts

Posted - 2002-08-23 : 11:55:49

Great job guys!! you rock!!

Whenever I start to think, my brain freezes therefore I think I'm cool...

xega
Starting Member

10 Posts

Posted - 2002-08-23 : 11:58:26

BTW, Onamuji I tried your code but it does not work. Where is the sequences table?

Regards
Xega

Whenever I start to think, my brain freezes therefore I think I'm cool...

CSantos
Starting Member

7 Posts

Posted - 2002-08-23 : 12:26:22

I don't now if is this that you want but ....

INSERT INTO TESTE VALUES ('A', 0)
UPDATE TESTE SET Ind = (SELECT COUNT(*) FROM TESTE WHERE COL = (SELECT COL FROM TESTE WHERE IND = 0)) WHERE Ind = 0

INSERT INTO TESTE VALUES ('A', 0)
UPDATE TESTE SET Ind = (SELECT COUNT(*) FROM TESTE WHERE COL = (SELECT COL FROM TESTE WHERE IND = 0)) WHERE Ind = 0

INSERT INTO TESTE VALUES ('B', 0)
UPDATE TESTE SET Ind = (SELECT COUNT(*) FROM TESTE WHERE COL = (SELECT COL FROM TESTE WHERE IND = 0)) WHERE Ind = 0

INSERT INTO TESTE VALUES ('B', 0)
UPDATE TESTE SET Ind = (SELECT COUNT(*) FROM TESTE WHERE COL = (SELECT COL FROM TESTE WHERE IND = 0)) WHERE Ind = 0

INSERT INTO TESTE VALUES ('B', 0)
UPDATE TESTE SET Ind = (SELECT COUNT(*) FROM TESTE WHERE COL = (SELECT COL FROM TESTE WHERE IND = 0)) WHERE Ind = 0

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-08-23 : 12:38:41

you need to make a Sequence table that has values from 0 through XX ... my generic sequence tables is 0 through 8192 ... thne that query will work fine ... though its not an update query it can give you ideas...

CREATE TABLE Sequences (Value INT NOT NULL PRIMARY KEY)

WHILE (SELECT ISNULL(MAX(Value), 0) FROM Sequences) < 8192
INSERT INTO Sequences SELECT ISNULL(MAX(Value), -1) + 1 FROM Sequences

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources