Fun with Sequence numbers - 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 2000 Forums

SQL Server Development (2000)

Fun with Sequence numbers

Author

Topic

dukey07
Starting Member

16 Posts

Posted - 2004-08-12 : 15:56:59

Hi all, having a brain freeze today, wondered if anyone can help on this one.

Here is an example of a data set I have.

RecID(Identity), AcctID, User
1, 1234, Jim
2, 2345, Bob
3, 2345, Joe
4, 3456, Phil
5, 3456, Phil
6, 3456, Fred

Now to adapt to some legacy code, I need it to look like the following.

RecID, AcctID, User, SequenceID
1, 1234, Jim, 1
2, 2345, Bob, 1
3, 2345, Joe, 1
4, 3456, Phil, 1
5, 3456, Phil, 2
6, 3456, Fred, 1

I thought there was a way to do this using a join to a "Sequence" table (i.e. Table of integers 0-N)

Any thoughts?

Dukey

Pat Phelan
Posting Yak Master

187 Posts

Posted - 2004-08-12 : 16:00:40

I'd use something like:

SELECT
   RecID, AcctID, User
,  (SELECT Count(*)
      FROM myTable AS b
      WHERE  b.AcctID = a.AcctID
         AND b.RecID <= a.RecID)
   FROM myTable AS a

-PatP

dukey07
Starting Member

16 Posts

Posted - 2004-08-12 : 16:09:55

Thanks a bunch PAt, that was quick. :)

I think I am actually looking for this:

SELECT
RecID, AcctID, UserName
, (SELECT Count(*)
FROM #mytable AS b
WHERE b.UserName = a.UserName
AND b.RecID <= a.RecID)
FROM #mytable AS a

but, you put me on the right track!
Thanks again,
Dukey

dukey07
Starting Member

16 Posts

Posted - 2004-08-12 : 16:24:01

Actually you need both for it to work.

SELECT
RecID, AcctID, UserName
, (SELECT Count(*)
FROM #mytable AS b
WHERE b.UserName = a.UserName
AND b.AcctID= a.AcctID
AND b.RecID <= a.RecID)
FROM #mytable AS a

Dukey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources