SQL Server ROW_NUMBER() on SQL Server 2000?

Question

I have a query that allows me to get records from a database table by giving it a minimum and maximum limit.

It goes like this:

  SELECT T1.CDUSUARIO, T1.DSALIAS, T1.DSNOMBRE_EMPRESA, T1.DSCARGO, T1.DSDIRECCION_CORREO, T1.CDUSUARIO_ADMINISTRADOR, T1.FEMODIFICACION 
    FROM (SELECT *, 
               ROW_NUMBER() OVER (ORDER BY CDUSUARIO) as row FROM TBL_USUARIOS ) as T1 
   WHERE row > @limiteInf 
     and row <= @limiteSup 
ORDER BY DSALIAS ASC;

Now, it works like heaven on SQL Server 2005 and SQL Server 2008 but tried to run it on an SQL Server 2000 database and says:

ROW_NUMBER it's an unknown function name or something like that.

What can I do??

Amphibrach

Answer 1

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

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

Edit 20 May 20011

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

Yadirayaeger

+1: The COUNT / self join also is more like using RANK/DENSE_RANK because duplicates will get the same value – Kava

"Neither solution will support PARTITION BY". You can GROUP BY when loading to temp table. Then add the IDENTITY to get id for each group. You can then join that temp table to your original query. – Marcelenemarcelia

Answer 2

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

Please see the original link here

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

Here is the below SQL for SQL Server 2000 to select the latest version of a record 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 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

Powerboat

Answer 3

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

Acrolein

this doesn't seem very helpful. he obviously knows it isn't in 2000, the error message tells him this. – Treasonous

@nathan gonzalez: it really is the basic truth though: there is no elegant substitute – Yadirayaeger

Exactly. Downvoting becasue you dont like the truth is not helpfull either, you know. it IS the truth. The function was introduced later than 2000 because it WAS missing. There is no same performance substitute. – Acrolein

Answer 4

This is my solution to the problem:

declare @i int
declare @t table (row int, stuff varchar(99))
insert into @t
select 0,stuff from mytable -- <= your query
set @i=0
update @t set row=@i, @i=@i+1
select * from @t

Explanation:

1. create a memory table
2. insert data (your query) with the row number as 0
3. update the row number field with an int variable which is incremented in the same update for the next record (actually the variable is incremented first and then updated, so it will start from 1)
4. "select" the result from the memory table.

You may ask, why don't i use the variable in the select statement? It would be simpler but it's not allowed, only if there is no result. It's ok to do it in an update.

Singleton

Please explain what you are doing there – Feeble