large tables-large resultsets-want recordset in chunks-can it be done?

[XgrinderX]

Hello!

I am fairly new to SQL programming, but I have a fairly decent amount of knowledge about the basics. What I am trying to do is perform a search on a very large table (millions of rows) with the possibility that the result could contain thousands of rows. I want my recordset to only return 50 rows. I want to be able to view the first 50 rows OR the second 50 rows OR the 100th 50 rows. Does that make sense? I know how to do this using a temp table, but the method I know still requires the time to return ALL the rows to the temp table and then grab the ones I want. My goal is to reduce the search time to an acceptable level such that these searches will not timeout and will not take forever in a web application environment.

Is there a way to have a query search for a specific batch of rows within a valid recordset? Grabbing the first batch is easy - set rowcount 50 will only process the first 50 rows. BUT, now I want to be able to grab the NEXT 50 rows.

Any ideas?

-Greg

 

[Cagliostro]

you can use

select top xxx row1, ...rowN from ttt

only manipulating with top and order by you can select what you need. John Fill

ivfmd@mail.md

 

[XgrinderX]

John,

could you please be more specific in your query?

here is a sample....

SELECT * FROM tblUser ORDER BY iUserID

how would you modify this query to grab the second batch of 50 rows (rows 51-100)?

thanks for your help!

-Greg

 

[Cagliostro]

select top 50 from
(
select top 100 t1.* from
(
SELECT * FROM tblUser ORDER BY iUserID asc
) t1
order by t1.iUserID desc
) t
John Fill

ivfmd@mail.md

 

[Cagliostro]

sorry, one mistake:
select top 50 t.* from
(
select top 100 t1.* from
(.....
.....
John Fill

ivfmd@mail.md

 

[XgrinderX]

as is, that query gives an error:
"An ORDER BY clause is invalid in views, derived tables, and subqueries unless TOP is also specified."

if i add a top statement in the 3rd select, it appears to work, i.e.:

select top 50 t.* from
(
select top 100 t1.* from
(
SELECT top 100 * FROM tblUser ORDER BY iUserID asc
) t1
order by t1.iUserID desc
) t

i think this is on the right track! my only question now is how do i ensure that the final batch does not overlap with the next to last batch? for example, if the total number of rows is 185, then using this query, the final batch returns rows 136-185 instead of 151-185 which would be what i need.

thanks again for the help John!! sorry i emailed you earlier if you have any more ideas, they are very welcome!!

-Greg