[ACCEPTED]-SQL Server ROW_NUMBER() on SQL Server 2000?-sql-server-2000

Accepted answer
Score: 11
  • There is a COUNT(*) with SELF JOIN solution here that will scale badly
  • You can load a temp table with an IDENTITY column and read back but it's not guaranteed to work (can't find article on it, was told at an MS Seminar years ago)

Neither solution will support PARTITION 4 BY

I've not mentioned loop or CURSOR based 3 solutions which are probably worse

Edit 20 2 May 20011

Example demo of why IDENTITY won't 1 work:
Do Inserted Records Always Receive Contiguous Identity Values

Score: 4

I know this thread is bit old, but for anyone 10 else looking for same solution, I think 9 it will be useful to know that there is 8 a good solution for this problem.

Please 7 see the original link here

For those who do 6 not want to click on the link, I have copied 5 and pasted the code below. Again, credit 4 goes to original publisher

Here is the below SQL for SQL Server 3 2000 to select the latest version of a record 2 grouping by a single column.

SELECT *
  FROM (
    SELECT *, (
      SELECT COUNT(*)
        FROM MyTable AS counter
      WHERE counter.PartitionByColumn = MyTable.PartitionByColumn
        AND  counter.OrderByColumn >= MyTable.OrderByColumn
      ) AS rowNumber
    FROM MyTable
  ) AS r1
  WHERE r1.rowNumber = 1

Same code in 1 SQL Server 2005 would look like this:

SELECT * FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY PartitionByColumn 
    ORDER BY OrderByColumn DESC) AS rowNumber FROM MyTable) AS rw1 
  WHERE rw1.rowNumber = 1
Score: 2

Use another function or upgrade your database. ROW_NUMBER 3 did not exist back in the 2000 version of 2 the database. Point. Nothing you can do 1 about it.

More Related questions