Best Of FileMaker 13 SQL: New Clauses for ExecuteSQL

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:

SELECT “CompanyName”
FROM “Companies”

with the results:

Anderson Co.
Bullet Co.H
Hemmley Inc.
Bongo Co. (Smith, Jim)
Jameson, Inc.
Bethson, Dale 
James, Maggie
Firestat Co.
Marteson Environmental
Rankle Industries
Aranato, Shuji
Mitle, Todd
Jermaine, Dwight
Henn, Barbara
Hellern, David
Roberts, Robert

by applying the OFFSET clause we can designate an “artificial” starting point:

SELECT “CompanyName”
FROM “Companies”
OFFSET 8 ROWS

now returns:

Marteson Environmental
Rankle Industries
Aranato, Shuji
Mitle, Todd
Jermaine, Dwight
Henn, Barbara
Hellern, David
Roberts, Robert

There’s no difference between ROW and ROWS as far as the query goes, it just gives you the ability to write your query and apply some english grammar.  For example:

SELECT “CompanyName”
FROM “Companies”
OFFSET 8 ROW

and

SELECT “CompanyName”
FROM “Companies”
OFFSET 1 ROWS

won’t throw an error, they just look kinda’ funny.

FETCH FIRST [ n [ PERCENT ]  { ROWS | ROW } { ONLY | WITH TIES } ]

Fetch First is conceptually simple, in that we use is to limit the number of results in our query.  However, it’s arguments are a just bit more complicated.

The simplest form of the clause would be literally designating the number of rows like this:

SELECT “CompanyName”
FROM “Companies”
FETCH FIRST 5 ROWS ONLY

based on our previous results we’d now get:

Anderson Co.
Bullet Co.
Hemmley Inc.
Bongo Co. (Smith, Jim)
Jameson, Inc.

Performance Considerations

Loading the results of a completed query into an expression’s results has some noticeable overhead, so if they’re not all needed, then using FETCH FIRST to limit them can help speed things up considerably.  A typical use for this would be to see if there’s any records that exist in a table that match the criteria.  We don’t care how many, or what they are, just if there’s anything there.  One way we could do this is use a Count like this:

SELECT COUNT ( id )
FROM “Companies”
WHERE “CompanyName” LIKE ?

This works, but doesn’t perform that well.  On 100,000 records it takes about 14 seconds to run on my LAN set up.  We can try it without the count like this:

SELECT id 
FROM “Companies”
WHERE “CompanyName” LIKE ?

That does much better, taking about 3 seconds.  We do need then to wrap our results in IsEmpty() to get a boolean result if anything is there, but we know how to do that.  However, if we add a FETCH FIRST clause like this:

SELECT id 
FROM “Companies”
WHERE “CompanyName” LIKE ?
FETCH FIRST ROW ONLY

It’s virtually instant.  Also, notice how we left out the n argument designating the number of rows.  This argument is optional, and when left unspecified, it will default to 1.  We can also get a little fancier here and set our query up to return the boolean result we’re after like this:

SELECT CASE WHEN id IS NOT NULL THEN 1 END
FROM “Companies”
WHERE “CompanyName” LIKE ?
FETCH FIRST ROW ONLY

ONLY vs WITH TIES

You must always designate either ONLY or WITH TIES when using FETCH FIRST or your query will generate an error.  ONLY puts a hard limit on the number of rows returned.  WITH TIES is used with the ORDER BY Clause and will allow the inclusion of additional results if they’re not distinct from ones included in the specified FETCH FIRST n argument.

Suppose we had duplicates in our original results, and we modified our original query to look like this with the ORDER BY clause added.

SELECT “CompanyName”
FROM “Companies”
ORDER BY “CompanyName” ASC

we’d now get:

Anderson Co.
Anderson Co.
Aranato, Shuji
Bethson, Dale 
Bongo Co. (Smith, Jim)
Bongo Co. (Smith, Jim)
Bullet Co.
Bullet Co.
Firestat Co.
Hellern, David
Hemmley Inc.
Hemmley Inc.
Henn, Barbara
James, Maggie
Jameson, Inc.
Jermaine, Dwight
Marteson Environmental
Mitle, Todd
Rankle Industries
Roberts, Robert

If we apply FETCH FIRST with ONLY like this:

SELECT “CompanyName”
FROM “Companies”
ORDER BY “CompanyName”
FETCH FIRST 5 ROWS ONLY

we do just get the 5 rows like this:

Anderson Co.
Anderson Co.
Aranato, Shuji
Bethson, Dale 
Bongo Co. (Smith, Jim)

However, if we run the same query with WITH TIES like this:

SELECT “CompanyName”
FROM “Companies”
ORDER BY “CompanyName”
FETCH FIRST 5 ROWS WITH TIES

we get:

Anderson Co.
Anderson Co.
Aranato, Shuji
Bethson, Dale 
Bongo Co. (Smith, Jim)
Bongo Co. (Smith, Jim)

Notice the 6th line!  We get that because it’s not distinct from the line above it that’s within the 5 count designated.  The WITH TIES argument can only be used when the Query has an ORDER BY clause, otherwise you’ll get an error.  Notice also, that in both cases, the FETCH FIRST is applied after the ORDER BY clause.

PERCENT

We can also designate a percentage, rather than a specific row count.

SELECT “CompanyName”
FROM “Companies”
FETCH FIRST 15 PERCENT ROWS ONLY

will return:

Anderson Co.
Bullet Co.
Hemmley Inc.

WITH TIES is also compatible with PERCENT, but remember you need the ORDER BY Clause as well:

SELECT “CompanyName”
FROM “Companies”
ORDER BY “CompanyName” ASC
FETCH FIRST 15 PERCENT ROWS WITH TIES

will return:

Anderson Co.
Anderson Co.
Aranato, Shuji

OFFSET and FETCH FIRST TOGETHER

These clauses also work great together to let you zero in on a specific slice of your results, so we can do something like this:

SELECT “CompanyName”
FROM “Companies”
OFFSET 3 ROWS
FETCH FIRST 15 PERCENT ROWS ONLY

which will return:

Bongo Co. (Smith, Jim)
Jameson, Inc.
Bethson, Dale

Returning 15 percent of the total rows, starting at row 4.

Powerful stuff, so happy slicing!