create sequence number for rowsets

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)

create sequence number for rowsets

Author

Topic

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2002-07-12 : 11:02:21

I have a table of denormalized data that needs a sequence number based on record or row groupings that are similar. For example I have a table with account number and date columns. These are key columns but not unique. In order to make them unique I need to add a sequence number. I would like to run through this table without using a cursor if I can help it but a solution is not coming to mind. I did some searches because I remember a similar problem that was discussed here a few months back but I cannot find it.

So basically the data looks like this

Account Date
AAA 1/1/2001
AAA 1/1/2001
AAA 2/1/2002
AAA 2/1/2002
BBB 1/1/2001
BBB 1/1/2001

and I need to add sequence numbers like this

Account Date SEQ
AAA 1/1/2001 1
AAA 1/1/2001 2
AAA 2/1/2002 1
AAA 2/1/2002 2
BBB 1/1/2001 1
BBB 1/1/2001 2

There could be as many as 16 sequence numbers in a set and the table has about 100K rows but will grow to 1M in the future.

Can someone point me in the right direction?

Cat

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-12 : 11:27:35


/*
create table #cat_jesus (account char(3), somedate smalldatetime)
insert #cat_jesus
select 'aaa','1/1/2001'
union all select 'aaa','1/1/2001'
union all select 'aaa','2/1/2002'
union all select 'aaa','2/1/2002'
union all select 'bbb','1/1/2001'
union all select 'bbb','1/1/2001'
*/

--step one, add the seq col
alter table #cat_jesus
    add seq tinyint
go

declare @seq tinyint, @lastaccount char(3), @lastdate smalldatetime
update #cat_jesus
set @seq = seq = case
                     when @lastaccount = account and @lastdate = somedate then @seq + 1
                     else 1
                 end,
    @lastaccount = account,
    @lastdate = somedate
go

select * from #cat_jesus
go

BUT . . . there is one problem. The data in cat_jesus must be ordered by account,somedate. Because the above example is a temp table, it will retain the order of the insert, but that is not guarrenteed. You may have to play around with working this in another working table where you can force the order and then somehow updating your original table. It's gonna depend on your ddl. My guess is you won't get this to work...

<O>

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2002-07-12 : 11:38:48

You would still need a method to run through all the records. I can put the records in sort order and the sequence column is already there, just unpopulated. But the real problem is not doing it but doing it utilizing set methodology.

Cat

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-12 : 11:46:47

quote:
You would still need a method to run through all the records.

Uhm...yeah...its called the UPDATE statement in the code provided.

<O>

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2002-07-12 : 13:37:48

Ah! I didn't realize you could do this "set @seq = seq = case.."

I think this might work. Interesting.

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources