Urgent required !!! Fetching Particular Row

Question

Hi all I am facing problem in fetching a range of rows from a table. For eg. I want to fetch from 5th row to 11th row of table without having an Identity column in table for refrence. I am using SQL SERVER 2000 . In SQL SERVER 2005 it is Possible but i am not able to do it in SQL 2000. And i know u smart people can answer me very well. Thanks in Advance.

Vijaya Kadiyala · Answer

Hi Check out the below link http://ehockeycoach.com/devgenuity/Articles/SQL/SQL2000Pagination.aspx Thanks -- Vj

Rafiq Batcha · Answer

Hi James answer is correct. I will elaborate that little bit detail. DECLARE @PageNum int SET @PageNum = 0 SELECT TOP 5 * FROM Product_Master WHERE ProdID Not in ( SELECT TOP (@PageNum*5) ProdID From Product_Master ORDER BY ProdID ) ORDER BY ProdID I am considering 5 rows per page. I have 6 rows. If I have to get 1st 5 rows send @pageNum as 0. For next 5rows send @pageNum as 1. I can get only 1 row now. The most imporatnt part here is Order By clause. You can use this inside the stored procedure. If you think that Order By column will be changed dynamically based on the user input go with Dynamic SQL.

Kasam Shaikh · Answer

opppssss not working buddy.....Thanks for reply...More suggestion will be appreciated

Niradhip Chakraborty · Answer

as you are stuck into SQL2k you have to use something like this here (getting the thrid row) SELECT TOP 1 * FROM SomeTable WHERE SomeKey Not in ( SELECT TOP 2 SomeKey From SomeTable ) //say ur someteble=employee and somekey=emp_int_id;

Kasam Shaikh · Answer

By applying the above code i am getting this error message; 'rownumber' is not a recognized function name.

Kasam Shaikh · Answer

Hi buddy thanks for replying but r u sure this will work in SQL Server 2000. As i am unable to do it in Server 2000.

Manish Dwivedi · Answer

Hi For getting the range of records from the database write the query like that. SELECT * FROM (SELECT Phone rownumber() OVER (ORDER BY Phone) AS rowNum FROM TableName) AS tableRow WHERE rowNum BETWEEN 10 and 20 Happy Coding!!