Post reply

ranking functionality in SQL 2000

  • December 8, 2010 at 7:18 am

    #244064

    Hi,

    I have used ROW_NUMBER() ranking function in a procedure (SQL 2008) for filtering purpose.

    Now I want to do the same functionality in SQL 2000.

    How to do this?

    Thanks

    VinothKumar.k

  • December 8, 2010 at 7:29 am

    #1260090

    The closest equivalent would be create a temptable with an identity column.

    Naturally , that will not support the over clause though


    Clear Sky SQL
    My Blog[/url]

  • December 8, 2010 at 7:33 am

    #1260095

    Hi

    There is no such options in 2000

    In 2000 you can use IDENTITY (data_type [ , seed , increment ] ) .Is used only in a SELECT statement with an INTO table clause to insert an identity column into a new table.

    It is somewhat as Row_number() with CTE

    USE AdventureWorks;

    GO

    --- 2005/8

    WITH OrderedOrders AS

    (

    SELECT SalesOrderID, OrderDate,

    ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'

    FROM Sales.SalesOrderHeader

    )

    SELECT *

    FROM OrderedOrders

    WHERE RowNumber BETWEEN 50 AND 60;

    --- 2000

    Select IDENTITY(int, 1,1) AS RowNumber,* into #Tmp from Sales.SalesOrderHeader

    ORDER BY OrderDate

    Select * from #Tmp WHERE RowNumber BETWEEN 50 AND 60;

    Thanks

    Parthi

    Thanks
    Parthi

  • December 8, 2010 at 10:29 pm

    #1260429

    hi

    If it is possible group by Orderdate

    That’s means order date wise row number

  • December 9, 2010 at 2:24 am

    #1260462

    Hi

    The ranking functions in SQL like Row_Number, Dense_rank, Rank are not supported in the 2000 version. It was introduced in 2005 version.

  • December 9, 2010 at 2:27 am

    #1260463

    Hi

    Pls refer this link for a solution

    http://www.codeproject.com/KB/reporting-services/Rank_Query.aspx

  • December 9, 2010 at 2:42 am

    #1260469

    vinoth-750003 (12/8/2010)

    hi

    If it is possible group by Orderdate

    That’s means order date wise row number

    Not without a cursor / looping solution


    Clear Sky SQL
    My Blog[/url]

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

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