Skip to content
«
»
 

Simulate ROW_NUMBER in SQL 2000

Posted by 
on  May 23 2010

You are currently browsing comments. If you would like to return to the full story, you can read the full entry here: “Simulate ROW_NUMBER in SQL 2000”.

Filed under SQL Server 2000
Comment (RSS)  |  Trackback  |  Permalink

4 Comments

  1. Dimitrije says:
    October 5, 2011 at 7:31 am

    Is there any way to achieve over partition by effect in SQL 2000?

    Reply to this comment
    • Derek Dieter says:
      October 10, 2011 at 9:10 am

      Hi Dimitrieje,

      Yes there is. Itzik Ben-Gan has an excellent example of this in his T-SQL Querying 2005 book. I highly recommend this book.

      Here’s the example:

      SELECT mgrid, empid, qty,
      (SELECT COUNT(1)
      FROM dbo.Sales AS S2
      WHERE s2.mgrid = s1.mgrid
      AND (s2.qty < s1.qty
      OR (s2.qty = s1.qty AND s2.empid <= s1.empid))) AS rownum
      FROM dbo.Sales AS s1
      ORDER BY mgrid, qty, empid;

      Reply to this comment
      • Ayrton says:
        May 15, 2012 at 6:26 am

        SQL can execute quriees against a database, retrieve,update,insert data from a database and empty refer to an empty result of no rows and NULL refer to a table with one row holding a NULL value.

        Reply to this comment
  2. Mangala says:
    February 23, 2011 at 9:43 pm

    Hi Derek,

    I found a way to get an Identity for the table by using a dummy field as so (I am assuming your id is an int):

    SELECT
    RowNumber = IDENTITY(INT,1,1)
    ,c.LastName
    ,c.FirstName
    ,c.CustomerID*1 as CustomerID
    INTO #Customer_RowID
    FROM SalesLT.Customer c
    ORDER BY c.LastName ASC

    Regards,

    Reply to this comment

post a comment OR Post Your Question on our ASK! Community!

«
»