Advantage Developer Zone


Limiting Query Results with START AT

Thursday, August 11, 2011

Limiting Query Results
Advantage 10 introduced a new clause to the SQL engine to help limit query results returned when executing a SELECT statement.  While the SQL engine already had the TOP clause to help in this regard, the new START AT clause helps to provide even greater versatility that can be useful for implementing paging functionality in an application.
The following is an example of using the new clause:
SELECT TOP <x> START AT <y> <column list> FROM <table> 
Where <x> must be greater than or equal to 0 and <y> must be greater than 0.  
If an ORDER BY clause is included in the query the server must populate the entire cursor to determine the order of the records prior to returning results.  Without an ORDER BY clause the server will stop processing the query once it has determined the number of rows necessary for the result set.
Usage of the TOP clause will force all queries to return as static cursors
Basic Example
The following query will return the first 10 records from the employee table:
SELECT TOP 10 START AT 1 * FROM employee
(The above statement is also equivalent to SELECT TOP 10 * FROM employee)
The following query will return rows 11 through 20 (assuming there are enough rows to return)
SELECT TOP 10 START AT 11 * FROM employee
Example usage in a PHP page
The following is a sample web page using PHP to connect and retrieve a list of 10 employees per page.
<title>Employee List</title>
<h1>Employee List</h1>
if (isset($_GET["page"]))
	(is_numeric($_GET['page'])) ? $currentPage = $_GET['page'] : $currentPage = 1;
	$currentPage = 1;

($currentPage == 1) ? $startAt = 1 : $startAt = (($currentPage-1)*10)+1;

$rConn = ads_connect("DataDirectory=c:\\ads\\db\\freeadt;ServerTypes=2", "", "");

$rResult = ads_do($rConn, "SELECT TOP 10 START AT $startAt lastname, firstname FROM employee ORDER BY lastname, firstname;");

echo "<table border=1><tr><th>Last Name</th><th>First Name</th></tr>";
while (ads_fetch_row($rResult))
	echo "<tr><td>" . ads_result($rResult, "lastname" ) . "</td><td>" . ads_result($rResult, "firstname") . "</td></tr>";	
echo "</table>";

$rResultCount = ads_do($rConn, "SELECT COUNT(*) from employee");
while (ads_fetch_row($rResultCount))
	$count = ads_result($rResultCount, "expr");

$numPages = ceil($count/10);
echo $numPages . "<br/>";
echo "<br />";
	echo "<a href='phpStartAt.php?page=" . $i . "'>" . $i . "</a> | ";		

As we can see from the example above, setting up paging can be quickly accomplished using the START AT clause.  In addition, the example above could be further expanded on by including a WHERE clause in conjunction with the CONTAINS scalar function to provide a nice paged view of search results.