Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 getting subset of main query!!

Author  Topic 

ravi_cpt
Starting Member

1 Post

Posted - 2004-07-18 : 23:52:08
hi all,

i need to load a page which contains some 1000 records to display. i am planning to introduce pagination in this so that at one time i retrieve only a set of 100 records.

my question is, is there any way of retrieving a subset of records from main query as follows :

for first iteration the following query format to be used to retrieve first 100 records
select * from <mainTbl> where rowindex between 1 and 100

then, for second iteration
select * from <mainTbl> where rowindex between 101 and 200

note :
this feature is required as the primary unique field might have varying values and we cannot use that field in WHERE clause.

thanks
-ravig

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-07-19 : 00:26:56
What programming language are you using at the front-end?

If it's VB/C++/.NET etc, you can use the ADO recordset paging function. This does essentially what you need. Check out the ADO documentation for more info.





Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-19 : 00:43:06
You can do this with a temp table and rowcount also.

For example:
--Second pass:
---SET ROWCOUNT = 200, insert into temp table with an identity column. Select the top 100 by identity DESC
--Third pass:
---SET ROWCOUNT = 300, insert into temp table with an identity column. Select the top 100 by identity DESC


MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page
   

- Advertisement -