Alternative of ROW_NUMBER() in sql server 2000

  • 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

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

  • 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
  • Thanks Christopher,

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

    Thanks for quick response

    Pravin V. Kadam

  • 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,

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

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