One of the less obvious, but exciting new features in FileMaker 13 is the two new clauses available in FileMaker’s SQL syntax. It’s less obvious, because you need to read the new FileMaker SQL Reference Guide to see they’ve been added.
(Here is the FileMaker 13 SQL Reference. All FM13 docs are here.)
The new clauses are OFFSET and FETCH FIRST. We’ve been asking for these functions in the FMSQL engine since the plug-in days, and they’re finally here. They give you the ability to slice your results up in different ways right within the query, as well as improve performance in certain situations.
OFFSET n [ ROW | ROWS ]
Offset is simple. It allows you to skip a designated number of rows and display your results from there. Consider the query:
with the results:
Bongo Co. (Smith, Jim)
by applying the OFFSET clause we can designate an “artificial” starting point:
OFFSET 8 ROWS
via Best of FileMaker 13 SQL- New Clauses for ExecuteSQL.