RowNumber in sql server 2000

In sql server 2000, we don’t have an inbuilt option to retrieve the data based on the rownumber. In sql server 2005, Rownumber() is used to implement such functionality.

In sql server 2000, we have to insert into a new table with identity column and after wards we have retrieve the result set based on the rownumber we want.
In the below code, I am inserting the rows into the table variable “@OutputTable” based on the result set order by address.

Code Snippet

DECLARE @VenkatTable TABLE (ID int, NAME varchar(50), ADDRESS varchar(50)) INSERT INTO @VENKATTABLE VALUES (1, 'Hari', 'Chennai') INSERT INTO @VENKATTABLE VALUES (2, 'Ram', 'Hyderabad') INSERT INTO @VENKATTABLE VALUES (3, 'Raj', 'Bombay') INSERT INTO @VENKATTABLE VALUES (52, 'Arun', 'Delhi') INSERT INTO @VENKATTABLE VALUES (53, 'Santhi', 'Dharmapuri') DECLARE @OutputTable TABLE ( RowID int identity primary key, Name varchar(50), Address varchar(50) ) DECLARE @RowID int INSERT INTO @ OutputTable (Name, Address) SELECT Name, Address from @VenkatTable Order by Address SELECT @RowID = RowID FROM @ OutputTable WHERE Name = 'Arun'; SELECT * FROM @ OutputTable WHERE RowID = @RowID

Happy learning……

Regards,
Venkatesan Prabu . J