Pulling groups of records.

[l0knezmonstir]

I would like to be able to get groups of records in sets I guess. I dont know the proper description for this, but basically I want to be able to take a query and say, give me the first 50, then say, give me just the next 50 and so on. I figure I can pull this off with TOPs and INTERSECTs, but I figure there has to be a better way to do this.

 

[donutman]

There is no good way to do it (afaik). A combination of TOP and setting a variable to the last PK read is the best way. The @@RowCount system variable can be used instead of TOP.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[stevexff]

There's no real easy way to do this, as the SQL calls are essentially stateless. So it's not quite as simple as just saying 'give me the next 50', as you have to specify ORDER BY on the SELECT so the returned rows always come back in the same order, and pass in enough predicates to allow the query to reposition. Unless the columns are in the index in the right order, this will cause you to materialise a potentially large cursor each time you call.

I guess you want to do this on a web server, delivering a page at a time? Unless you have a very large potential result set, you might be better off fetching them all, cacheing the result set on the server, and then just paging through the cached copy on subsequent GET requests.

Another possibility worth considering is to return a maximum of (say) 100 rows with a message to the user asking them to refine their search argument.

 

[JamesLean]

FYI, if you do a search for "paging" on this forum, any forums relating to the coding language you are using as well as a general internet search you should find plenty of information on it. It is a subject that crops up time and time again!

--James

 

[l0knezmonstir]

This is all kinda what i figured. This is for a Delphi desktop app. So it sounds like this is going to be done the hard way. Its not really a huge issue, but we would like to make sure that the app will perform well, even if the recordset hits a large number. The biggest problem i had was not knowing what this problem was called. Thanks for the help.