Add row number that alternates between groups of numbers

Question

Post reply

Add row number that alternates between groups of numbers

samalex

SSCertifiable

Points: 5429
More actions
June 25, 2010 at 8:25 am

#100268

If this were SQL 2008 it's be simple, but I'm on a SQL 2000 system and need to add a Row Number that groups by a given field.
So given the AcctNo i need to populate the RowNum with something similar to this:
AcctNoRowNo
1231
1231
1242
1253
1264
1264
1275
1296
1307
The examples I've found on how to do this work to some degree, but here's the query I have thus far:
SELECT (SELECT SUM(1) FROM @tmpTable WHERE AcctNo<= reg.AcctNo) AS RowNo,
AcctNo
FROM @tmpTable reg
but it returns this:
AcctNoRowNo
1232
1232
1243
1254
1266
1266
1277
1298
1309
I need the RowNo field to be sequential.
Thanks for any insight on a simple way to do this. Also note the AcctNo values may not be sequential.
Thanks --
Sam

Viewing 7 posts - 1 through 6 (of 6 total)

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

Ken McKelvey SSCoach Points: 18853 More actions · Answer 1

Try something like:

(SELECT COUNT(DISTINCT AcctNo) FROM @tmpTable WHERE AcctNo<= reg.AcctNo) AS RowNo,

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

SELECT AcctNo, RowID = IDENTITY(INT,1,1)

INTO #Temp

FROM (

SELECT 123 AS AcctNo UNION ALL

SELECT 123 UNION ALL

SELECT 124 UNION ALL

SELECT 125 UNION ALL

SELECT 126 UNION ALL

SELECT 127 UNION ALL

SELECT 129 UNION ALL

SELECT 130) d

GROUP BY AcctNo

ORDER BY AcctNo

SELECT d.*, t.RowID

FROM (

SELECT 123 AS AcctNo UNION ALL

SELECT 123 UNION ALL

SELECT 124 UNION ALL

SELECT 125 UNION ALL

SELECT 126 UNION ALL

SELECT 127 UNION ALL

SELECT 129 UNION ALL

SELECT 130) d

INNER JOIN #Temp t ON t.AcctNo = d.AcctNo

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

samalex SSCertifiable Points: 5429 More actions · Answer 3

SSChasing Mays, that's exactly what I was looking for. Thanks... I didn't think to use Distinct.

SSCrazy, sorry for not following etiquette. I thought I covered all my bases by posting exact examples of what I was trying to do with results, but after reading through the page you linked to I see how I could've been more exact.

Take care and thanks again for the assistance ...

Sam

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

Ken McKelvey (6/25/2010)
Try something like:
(SELECT COUNT(DISTINCT AcctNo) FROM @tmpTable WHERE AcctNo<= reg.AcctNo) AS RowNo,

Try that on a single account of, say, 20000 rows and see how long it takes. Then read the following for why it takes so long...

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

--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)

samalex SSCertifiable Points: 5429 More actions · Answer 5

Hi Jeff,

Great article, and yes I can see how running it against a large dataset could take some time. In my scenario though we're just doing a few hundred rows in a report, and after plugging in Ken's suggested code the speed is actually pretty good. If we do need to run this against a larger set of data I'll definitely change it to suit.

Thanks again...

Sam

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

samalex (6/25/2010)
Hi Jeff,
Great article, and yes I can see how running it against a large dataset could take some time. In my scenario though we're just doing a few hundred rows in a report, and after plugging in Ken's suggested code the speed is actually pretty good. If we do need to run this against a larger set of data I'll definitely change it to suit.
Thanks again...
Sam

Ok... but think of it this way. Would you justify having sugar in your gas tank just because you were only going to drive a couple of miles? Would you ignore your child's bloody nose just because there was only a little blood? Would you practice hitting the wrong keys on a piano just because the song was short? Would you knowingly drink from a glass that had just a little poison in it?

Even a Cursor and While Loop is safer AND FASTER than the method you're about to contaminate your database with. 😉

--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)