Alternative of ROW_NUMBER() in sql server 2000

Question

Post reply

Alternative of ROW_NUMBER() in sql server 2000

aks_pra

Mr or Mrs. 500

Points: 591
More actions
December 17, 2008 at 5:41 am

#203055

Hi,
Please let me know how to use ROW_NUMBER() function in sql server 200,
i know how to use in sql 2005..
It gives error in SQL server 2000
Looking for the quick and affirmative response.
Thanks and Regards,
Pravin Kadam

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

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

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 1

There is no row number in SQL 2000

What is it you trying to do?

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

aks_pra Mr or Mrs. 500 Points: 591 More actions · Answer 2

Hi ,

I have onc date column in that I have transition date for each customer.

I have to find out the difference between the dates per customer.

eg :-

CUST_ID date

XXX 12-05-2008

XXX 12-08-2008

XXX 12-25-2008

XXX 12-28-2008

If the difference between the dates (Pervious and next ) is greater than 4 then I have take the sum from the pervious date to last date.

Please suggest me the answer

Thanks and Regards,

Pravin V. Kadam

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 3

try something like this.

DECLARE @YourTable TABLE

(Cust_ID INT,Date DATETIME)

INSERT INTO @YourTable

SELECT 1,'2008-12-05' UNION ALL

SELECT 1,'2008-12-08' UNION ALL

SELECT 1,'2008-12-25' UNION ALL

SELECT 1,'2008-12-28'

DECLARE @MyTable TABLE

(id INT IDENTITY(1,1),Cust_ID INT,Date DATETIME)

INSERT INTO @MyTable (Cust_ID,Date)

SELECT * FROM @YourTable

SELECT DISTINCT a.*

FROM @MyTable a

INNER JOIN @MyTable b ON b.id <= a.id

WHERE DATEDIFF(dd,b.Date,a.Date) > 4

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

aks_pra Mr or Mrs. 500 Points: 591 More actions · Answer 4

Thanks Christopher,

Let me run the code, i will get back to you.

Thanks for quick response

Pravin V. Kadam

keyun SSCrazy Points: 2397 More actions · Answer 5

Christopher,

Its very nice query but here is my issue which is little bit different than above,

I am having same issue on SQL 03 where I can not use Row_Number()

What I have in my table is below:

IDFnameLnameAmount

1SmithJohnson$12.32

1SmithJohnson$23.32

2MelindaBen$23.09

2MelindaBen$45.32

2MelindaBen$566.00

And here is what I am trying to accomplish:

IDID_Line_NoFnameLnameAmount

11 SmithJohnson$12.32

12 SmithJohnson$23.32

21 MelindaBen$23.09

22 MelindaBen$45.32

23 MelindaBen$566.00

I would like to get "ID_Line_No" column where number increse when ID number change. 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,