Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Alternative of ROW_NUMBER() in sql server 2000 Expand / Collapse
Author
Message
Posted Wednesday, December 17, 2008 5:41 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 10:07 AM
Points: 27, Visits: 74
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

Post #621115
Posted Wednesday, December 17, 2008 5:44 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 24, 2010 4:10 AM
Points: 1,553, Visits: 2,232
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


Posting Best Practices
Numbers / Tally Tables

SQL-4-Life
Post #621120
Posted Wednesday, December 17, 2008 5:56 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 10:07 AM
Points: 27, Visits: 74
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






Post #621126
Posted Wednesday, December 17, 2008 6:04 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 24, 2010 4:10 AM
Points: 1,553, Visits: 2,232
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


Posting Best Practices
Numbers / Tally Tables

SQL-4-Life
Post #621136
Posted Wednesday, December 17, 2008 6:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 10:07 AM
Points: 27, Visits: 74
Thanks Christopher,

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

Thanks for quick response

Pravin V. Kadam




Post #621140
Posted Tuesday, January 26, 2010 2:11 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 17, 2012 6:16 AM
Points: 89, Visits: 236
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:
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 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,

Post #853963
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse