The FETCH clause filters SELECT or COLLECT results to only the indicated number of records. Also applies to EXECUTE and VALUES. FETCH is often used with ORDER BY to fetch only part of a results table ordered by some field.
SELECT <field>, ... FROM <table> FETCH <value>
Suppose we write a query using the mfd_root table built into every Manifold project.
SELECT * FROM [mfd_root];
A basic SELECT with no FETCH clause returns all records in the table.
SELECT * FROM [mfd_root] FETCH 5;
Adding the FETCH clause with a value of 5 reduces the number of records returned to only the first five records returned.
FETCH normally comes last in a SELECT statement, even after an ORDER BY.
SELECT * FROM [mfd_root]
ORDER BY [NAME];
In the example above, we use ORDER BY to order the results table by the NAME column. If we want only the first five records, we can add a FETCH after the ORDER BY:
SELECT * FROM [mfd_root]
ORDER BY [NAME] FETCH 5;
EXECUTE - FETCH is often used with EXECUTE and EXECUTE WITH against a data source using ON. If we do not know how many records will be returned, we can guard against accidentally asking for a billion records by using FETCH to limit the number of records returned to whatever reasonable value we expect.