How to get the current row number in an SQL Server 2000 query?

Question

How do I get the row number in an SQL query using SQL Server 2000, where the ROW_NUMBER() function is not supported?

Bautista

Answer 1

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>

Affricate

Except, this is not a temp table but a table variable, and they are not support in 200 either – Schistosome

Table variables are supported in SQL Server 2000 – Alkalinize

Answer 2

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

Alkalinize

Answer 3

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

Eec

Answer 4

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

Coercion

Answer 5

Another way of doing this without using a SQL defined function could be the following:

SELECT 
(SELECT COUNT(1) + 1 FROM YourTable t2 WHERE t2.Id < t.Id) AS RowNumber
FROM YourTable t

It's a bit tricky, but seems simpler that the options that others gave you.

Trapeziform

Answer 6

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

Bautista

This will give you the count of the number of items smaller or equal to the current item in the list, but will not give you the unique row_number, if there are duplcate items – Affricate