|
SQL Server > Current Row in SQL Server 2000 articles: • Returning the current row number as part of a query in SQL Server 2000
|
Return to index of articles
Returning the current row number as part of a query in SQL Server 2000
Category: SQL Server
Category: Current Row in SQL Server 2000
SQL Server 2005 has implemented the ROW_NUMBER() function, and Oracle has ROWNUM, what to do if you're using SQL Server 2000? There are two possible options, one uses a count, the other a temporary table. Try both with your data to see which performs better.
Temporary table method:
CREATE TABLE #t (row_number int IDENTITY,
col1 ....)
INSERT #t (col1, ... )
SELECT col1, ...
FROM ...
ORDER BY
Count method:
SELECT
rank = (
SELECT COUNT(*)
FROM people b
WHERE
a.lastname > b.lastname
OR
(
a.lastName = b.lastName
AND a.firstName >= b.firstName
)
),
a.firstName,
a.lastName
FROM
people a
ORDER BY
a.firstName,
a.lastName
3/1/2008
|
