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:

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

SELECT “CompanyName”
FROM “Companies”
OFFSET 8 ROWS

now returns:

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:

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:

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:

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:

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:

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:

OFFSET and FETCH FIRST TOGETHER

FileMaker 13 SQL Explorer

Need Help? Try SeedCodes FREE SQL Explorer

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:

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

Powerful stuff, so happy slicing!

Tagged with →  

7 Responses to Best Of FileMaker 13 SQL: New Clauses for ExecuteSQL

  1. You ROCK as always Jason! Thank you!

  2. Excellent description, Jason. Thanks.

  3. Saigopal Das says:

    Excellent. This will be helpful

  4. Priyabrata Sahoo says:

    Nice description.
    Good to see some new and powerful stuffs been added into File maker SQL armory.

  5. Kevin Frank says:

    Fantastic and highly useful writeup. Thanks so much.

  6. Brian Schick says:

    Thanks so much for this, Jason. This (and you) rock :-)

  7. uli graser says:

    in fm12 the max/min via ExecuteSQL was reported to be slower then using ExecuteSQL on the set of dates and sort via ORDER then fetch first row – if it is true. even the MAX MIN should run linear and SORT/ORDER BY runs in O ( n log n ) ideally – this could be another faster MAX MIN via fetch first row – what do you think? thanx for great blog!!!

Leave a Reply

Your email address will not be published. Required fields are marked *

Share →
DOWNLOAD
DayBack Calendar
DayBack's 30-day trial is unlocked so you can customize it and integrate it with your files.
Download DayBack and we'll send you a couple short emails with tips on how to modify it and use some of the coolest features.
Thank you! Please download: DayBack Calendar
Need More?
SeedCode tips & example files in your inbox
Need More?
SeedCode tips & example files in your inbox
Want More?
Be the first to see articles and tips like these
DOWNLOAD
TimeZync
Download TimeZync and we'll send you a couple short emails with tips syncing your FileMaker Go files.
Thank you! Please download: TimeZync
Want More?
Be the first to see articles and tips like these