Geek Answers Handbook

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

How to get line number in SQL query using SQL Server 2000, where function is ROW_NUMBER()not supported?

+5
source share
5 answers

You can always try to use a temporary 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 searches I've seen in Sql Server 2000 use this template.

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>
+6
source

Another way to create a temp table using id:

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

Row_Number() Sql Server 2000 - 2005 .

Row_Number , , Sql 2000:

+1

, ?

SELECT (SELECT SUM (1)

FROM sample_source_ref

WHERE sampleen_source_rcd <= reg.specimen_source_rcd

) AS 'Row Number'

*

sample_source_ref reg

0

More articles:


All Articles