SQL Reference

Chapter 2 | Supported standards 13
ORDER BY clause
The ORDER BY clause indicates how the records are to be sorted. If your SELECT statement
doesn’t include an ORDER BY clause, the records may be returned in any order.
The format is:
ORDER BY {sort_expression [DESC | ASC]}, ...
sort_expression can be the field name or the positional number of the column expression to
use. The default is to perform an ascending (ASC) sort.
Examples
Sort by last_name then by first_name.
The second example uses the positional numbers 2 and 3 to get the sa
me ordering as the
prior example that specified last_name and first_name explicitly.
SELECT emp_id, last_name, first_name FROM emp ORDER BY 2,3
Note FileMaker Server uses a unicode binary sort order, which is different from language sorting
in FileMaker Pro or with the default language-neutral sort order.
OFFSET and FETCH FIRST clauses
The OFFSET and FETCH FIRST clauses are used to return a specified range of rows beginning
from a particular starting point in a result set. The ability to limit the rows retrieved from large result
sets allows you to “page” through the data and improves efficiency.
The OFFSET cla
use indicates the number of rows to skip before starting to return data. If the
OFFSET clause is not used in a SELECT statement, the starting row is 0. The FETCH FIRST clause
specifies the number of rows to be returned, either as an unsigned integer greater than or equal
to 1 or as a percentage, from the starting point indicated in the OFFSET clause. If both OFFSET
and FETCH FIRST are used in a SELECT statement, the OFFSET clause should come first.
The OFFSET and FE
TCH FIRST clauses are not supported in subqueries.
OFFSET format
The
OFFSET format is:
OFFSET n {ROWS | ROW} ]
n is an unsigned integer. If n is larger than the number of rows returned in the result set, then
nothing is returned and no error message appears.
ROWS is the same as ROW.
SELECT emp_id, last_name, first_name FROM emp ORDER BY last_name, first_name