Row Numbering

I came across a precarious situation today at work. I needed to write a paging stored procedure for use with a DetailsView. The situation and solution for start to finish is described at http://microsoft.apress.com/index.php?id=51. Unfortunately the sql database was currently running on an instance of sql 2000. Row_Number() is a specific function avaliable with sql 2005, not sql 2000.

The sql query I developed seems to work for an equivalent to Row_Number(). My case only had one sorting method, and I know that my column that is being sorted is unique. thus simplifying some matters.

Let's presume we have a Table1 with columns ID as an int, Code as a varchar, and Name. Table1 should be sorted by the column Code.


SELECT ID, Code, Name
(SELECT COUNT(ID) AS Expr1
FROM Table1
WHERE (Code < (SELECT Code FROM Table1 WHERE (ID = t.ID)))
) AS RowNum FROM Table1 AS t ORDER BY Code


Whilst this may not be very efficient or pretty, it gets the job done. Basically this query has a sub query to count the number of rows that are less than the current Code (which is selected by a further sub query).

Unorthodox probably, as bad as a GOTO statement maybe, works under the right circumstances.