How to put row number for sql query in sql 2000 where rownumber() is not supporting?

Question

How to put row number for sql query in sql 2000 where rownumber() is not supporting?

Answer 1

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/13daeb85-da43-4166-b188-595a4a5d5282

Answer 2

You can't use Row_Number() in Sql Server 2000 - it was introduced in 2005.

In case you wanted to use Row_Number for paging, here are some ideas on how to perform efficient paging in Sql 2000:

• Efficiently Paging Through Large Result Sets in SQL Server 2000
• A More Efficient Method for Paging Through Large Result Sets

Answer 3

You can always try to use a temp table with an identity column

DECLARE @table TABLE(
     [id] INT IDENTITY(1,1),
     Val VARCHAR(10)
)

DECLARE @TableFrom TABLE(
     Val VARCHAR(10)
)
INSERT INTO @TableFrom (Val) SELECT 'A'
INSERT INTO @TableFrom (Val) SELECT 'B'
INSERT INTO @TableFrom (Val) SELECT 'C'
INSERT INTO @TableFrom (Val) SELECT 'D'

INSERT INTO @table (Val) SELECT * FROM @TableFrom ORDER BY Val DESC
SELECT * FROM @table

Some of the best paging i have seen in Sql Server 2000 uses this pattern

DECLARE @PageStart INT,
     @PageEnd INT

SELECT  @PageStart = 51,
     @PageEnd = 100

SELECT  <TABLE>.*
FROM    (
      SELECT TOP (@PageStart - 1)
        <ID>
      FROM (
         SELECT TOP (@PageEnd)
           <ID>
         FROM TABLE
         ORDER BY <ID> ASC
        ) SUB
      ORDER BY SUB.<ID> DESC
     ) SUB INNER JOIN
     <TABLE> ON SUB.<ID> = <TABLE>.<ID>
ORDER BY SUB.<ID>

Answer 4

Could you elaborate how the below query will solve the problem?

SELECT ( SELECT SUM(1)

FROM specimen_source_ref

WHERE specimen_source_rcd <= reg.specimen_source_rcd

) AS 'Row Number'

,*

FROM specimen_source_ref reg

Answer 5

Another way to create a temp table with an identity to use:

SELECT Field1, Field2, IDENTITY(int, 1,1) AS MyID 
INTO #Temp 
FROM Table1

Answer 6

SELECT Field1, Field2, IDENTITY(int, 1,1) AS MyID
INTO #Temp
FROM Table1

too good :)