Alternate of Row_Number() in SQL 2000

Question

Alternate of Row_Number() in SQL 2000

keyun

SSCrazy

Points: 2397
More actions
January 27, 2010 at 6:58 am

#144554

I might be posting duplicate but I couldn't find the answer so apologies for that.
I am having issue on SQL 03 where I can not use Row_Number() function.
What I have in my table is below:
ID Fname Lname Amount
1 Smith Johnson $12.32
1 Smith Johnson $23.32
2 Melinda Ben $23.09
2 Melinda Ben $45.32
2 Melinda Ben $566.00
And here is what I am trying to accomplish:
ID ID_Line_No Fname Lname Amount
1 1 Smith Johnson $12.32
1 2 Smith Johnson $23.32
2 1 Melinda Ben $23.09
2 2 Melinda Ben $45.32
2 3 Melinda Ben $566.00
I would like to get "ID_Line_No" column where number increase when ID number change it should start again with number 1 so, as you see above that when ID = 1 and I have two records for Mr.Smith so, my code should identify each row starting with 1,2,...
It is easy in SQL 2005 but I have one client that use SQL 2003.
Please advice,

Viewing 11 posts - 1 through 10 (of 10 total)

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

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

What is the nature of the job? Is it for a report, a one-off migration or part of a business process which could be run at any time?

How many rows are in the table?

Do you have sufficient rights to create a new table?

What is the older version of SQL Server? Don't restate '2003'.

^{“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

Lowell SSC Guru Points: 323504 More actions · Answer 2

to do the equivilent of row-number() in 2000, I've always had to create a temp table with an extra column, insert the data into the table, then run an additional process to create the row_number() values, then finially select from that temp table.

here is an example, i believe from Jeff Moden from a post i participated in with him few years ago.

--===== Create a test table.

-- This is NOT part of the solution.

IF OBJECT_ID('TempDB..#YourTable') IS NOT NULL

DROP TABLE #YourTable

CREATE TABLE #YourTable

(

AccountNbr INT,

CreateDate DATETIME

)

--===== Populate the test table with data.

-- This is NOT part of the solution.

INSERT INTO #YourTable

(AccountNbr,CreateDate)

SELECT '59961','01/05/09' UNION ALL

SELECT '59961','01/06/09' UNION ALL

SELECT '59961','01/07/09' UNION ALL

SELECT '32187','01/05/09' UNION ALL

SELECT '32187','01/06/09' UNION ALL

SELECT '22195','01/10/09' UNION ALL

SELECT '22195','01/12/09' UNION ALL

SELECT '22195','01/13/09' UNION ALL

SELECT '22195','01/15/09' UNION ALL

SELECT '69248','01/11/09' UNION ALL

SELECT '69248','01/12/09'

--===== You might be tempted to use this, but it contains a

-- triangular join and will slow down in a hurry if you

-- have more than just a handful of rows for each AccountNbr.

-- I posted this to warn you that it's bad.

SELECT AccountNbr,

CreateDate,

Seq = (SELECT COUNT(*)

FROM #yourtable i

WHERE i.AccountNbr = o.AccountNbr

AND i.CreateDate <= o.CreateDate)

FROM #yourtable o

ORDER BY o.AccountNbr, o.CreateDate

--===== Instead, use the following method which will solve a

-- million rows in about 7 seconds.

IF OBJECT_ID('TempDB..#SeqWork') IS NOT NULL

DROP TABLE #SeqWork

DECLARE @PrevAccountNbr INT,

-- @PrevCreateDate DATETIME,

@PrevSeq INT

SELECT ISNULL(AccountNbr,0) AS AccountNbr,

ISNULL(CreateDate,0) AS CreateDate,

CAST(0 AS INT) AS Seq

INTO #SeqWork

FROM #yourtable

ORDER BY AccountNbr, CreateDate

ALTER TABLE #SeqWork

ADD PRIMARY KEY CLUSTERED (AccountNbr, CreateDate)

UPDATE #SeqWork

SET @PrevSeq = Seq = CASE WHEN AccountNbr = @PrevAccountNbr THEN @PrevSeq+1 ELSE 1 END,

@PrevAccountNbr = AccountNbr

FROM #SeqWork WITH(INDEX(0),TABLOCKX)

SELECT *

FROM #SeqWork

ORDER BY AccountNbr, CreateDate

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

keyun SSCrazy Points: 2397 More actions · Answer 3

Chris - It is DTS pkg that extract data from db. Yes, It is a DBA role and table might contain row between 20 - 1500. It is once a day process during off pick hours.

Lowell - thanks for the script I am about to TESTING out on my sample data and get back with you soon.

Appreciate you guys for quick response.

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

If the machine you do this on has more than one processor, you really need to add OPTION(MAXDOP 1) to the update query. It's also been determined that the index hint isn't essential... it just makes people more comfortable but does slow the code down quite a bit. So, replace the update in the code that Lowell posted with the following...

UPDATE #SeqWork

SET @PrevSeq = Seq = CASE WHEN AccountNbr = @PrevAccountNbr THEN @PrevSeq+1 ELSE 1 END,

@PrevAccountNbr = AccountNbr

FROM #SeqWork WITH(TABLOCKX)

OPTION (MAXDOP 1)

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

keyun SSCrazy Points: 2397 More actions · Answer 5

Thank You all for giving me your feed back.

I have used Powell option and it did work for me very smooth.

Again, Thank you all for helping me out. God Bles....U...

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 6

Lowell (1/27/2010)
to do the equivilent of row-number() in 2000, I've always had to create a temp table with an extra column, insert the data into the table, then run an additional process to create the row_number() values, then finially select from that temp table.

A slight twist on this is available:

DROP TABLE #NumberedSource

SELECT RowID = identity(INT,1,1), s.[Name], s.[Group]

INTO #NumberedSource

FROM (SELECT TOP 10 [Name], [Group] = 1

FROM master.dbo.syscolumns

ORDER BY [Name]) s

WHERE s.[Group] = 1

ORDER BY s.[Name]

SELECT * FROM #NumberedSource ORDER BY RowID

This gives you sequence numbers "on the way in", so to speak. The method is dependant upon SELECT...INTO, so you can't do this:

INSERT INTO #NumberedSource (RowID, s.[Name], s.[Group])

SELECT RowID = identity(INT,1,1), s.[Name], s.[Group]

FROM (SELECT TOP 10 [Name], [Group] = 2

FROM master.dbo.syscolumns

ORDER BY [Name]) s

WHERE s.[Group] = 2

ORDER BY s.[Name]

which would give you the logical equivalent of ROW_NUMBER() OVER(PARTITION BY...).

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

Heh... probably not enough coffee on my part, yet, but I'm missing it, Chris. How can you use the code you posted to produce the ID_Line_No column content that the OP requested?

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

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 8

Jeff Moden (3/7/2010)
Heh... probably not enough coffee on my part, yet, but I'm missing it, Chris. How can you use the code you posted to produce the ID_Line_No column content that the OP requested?

That is my point Jeff - you can't! At least, not as easily as you would hope to. Having the ID generated on the output column means your ID's are assigned exactly as you would expect according to your ORDER BY, but the properties of an ID column are observed in the receiving column, meaning you can't, for example, CAST the output ID column to something else to circumvent the limitations.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 9

Ah... see? That's what I meant... not enough coffee on my part. 🙂

The code that Lowell posted will do the trick and it's one of the reasons why I wrote the Running Total article... to be able to solve the "Grouped Ordinal Rank" problem in SQL Server 2000.

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

keyun SSCrazy Points: 2397 More actions · Answer 10

Humm...

I think the code that Lowell provided is working AWSOME for me and I ready put that one in production.

Thanks a Million LOWELL>