[ACCEPTED]-SQL Server ROW_NUMBER() on SQL Server 2000?-sql-server-2000
- 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
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
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