The ability to divide the results of a query into smaller pieces is a very common need for disconnected or partially connected applications. These smaller resultsets can be much more efficient than passing back the entire result and allow for paging. For example; It is very common to see controls like the ones shown below after a web search.
You can use the TOP function to limit the results of a query to a specific number of records or a percentage of the resultset. This command has been available since version 8. With version 10 we have added the START AT functionality which allows for paging. The basic syntax is TOP x START AT y, where x is the number of records to return and y is the first record in the result.
// Get the first 10 customers SELECT TOP 10 CustNum, LastName, FirstName FROM Customer ORDER BY LastName // Get the next 10 customers SELECT TOP 10 START AT 11 CustNum, LastName, FirstName FROM Customer ORDER BY LastName;
If you set the START AT value greater than the number of records an empty resultset will be returned. You cannot use parameters as the values, your logic must build the appropriate statement as you get additional pages.
For a demonstration of the new SQL features in Advantage 10 you can watch this screencast on the DevZone.
No comments:
Post a Comment