Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Alternative of ROW_NUMBER() in sql server 2000


Alternative of ROW_NUMBER() in sql server 2000

Author
Message
aks_pra
aks_pra
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
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

Christopher Stobbs
Christopher Stobbs
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1574 Visits: 2232
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
Numbers / Tally Tables

SQL-4-Life
aks_pra
aks_pra
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
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

Christopher Stobbs
Christopher Stobbs
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1574 Visits: 2232
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
Numbers / Tally Tables

SQL-4-Life
aks_pra
aks_pra
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 74
Thanks Christopher,

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

Thanks for quick response

Pravin V. Kadam
keyun
keyun
SSC Journeyman
SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)

Group: General Forum Members
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,
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search