How do I get the row number in an SQL query using SQL Server 2000, where the ROW_NUMBER()
function is not supported?
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>
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With