Q: SQL return only first match from join table I have a simple one to many table of jobs and statuses. Jobs can cycle back and forth in statuses during processing so a job could have more than one instance of a particular status. However, I need a report of that job as it hits just the first instance of particular statuses from start to finish. 
My initially constructed statement will return the data I need but for every matching instance in the join--however, I only need the first match. For example:
Job #, Date In, Invoice Date, Customer, OutForApproval Date, Approved Date, Due Date, Shipped Date.
The resulting data will return multiple rows from Table A (Jobs) but each join result, will only return the first match of OFA, Approved, Shipped, and Invoiced. Almost like a cross-tab report.
SELECT a."JobNumber" AS "Job Number", a."dateIn" AS "Date In", e."created" AS "Invoiced" , a."customer" AS "Customer", a."jobTitle" AS "Job Title", b."created" AS "OFA", c."created" AS "Approved", a."dateShip" AS "Date Due", d."created" AS "Shipped" 
FROM "@ Jobs" a
INNER JOIN "@ Statuses" b ON (a."ID"   = b."fkey" AND b."status" = 'OFA')
INNER JOIN "@ Statuses" c ON (b."fkey" = c."fkey" AND c."status" IN ('Offset Press','Indigo Press', 'Wide Format Press'))
INNER JOIN "@ Statuses" d ON (c."fkey" = d."fkey" AND d."status" IN ('Shipped', 'Partially Shipped'))
INNER JOIN "@ Statuses" e ON (d."fkey" = e."fkey" AND e."status" = 'Invoiced') 

WHERE a."currentStatus" = ? AND e."created" BETWEEN ? AND ?

ORDER BY a."JobNumber" ASC

It must conform to SQL 92. No MSSQL, Oracle or MySQL, no temp tables as this is not supported in FileMaker. 

A: Starting from FIleMaker 14 you can use "FETCH FIRST 1 ROWS ONLY"
SELECT a."JobNumber" AS "Job Number", a."dateIn" AS "Date In", e."created" AS "Invoiced" , a."customer" AS "Customer", a."jobTitle" AS "Job Title", b."created" AS "OFA", c."created" AS "Approved", a."dateShip" AS "Date Due", d."created" AS "Shipped" 
FROM "@ Jobs" a
INNER JOIN "@ Statuses" b ON (a."ID"   = b."fkey" AND b."status" = 'OFA')
INNER JOIN "@ Statuses" c ON (b."fkey" = c."fkey" AND c."status" IN ('Offset Press','Indigo Press', 'Wide Format Press'))
INNER JOIN "@ Statuses" d ON (c."fkey" = d."fkey" AND d."status" IN ('Shipped', 'Partially Shipped'))
INNER JOIN "@ Statuses" e ON (d."fkey" = e."fkey" AND e."status" = 'Invoiced')     
WHERE a."currentStatus" = ? AND e."created" BETWEEN ? AND ?    
ORDER BY a."JobNumber" ASC
FETCH FIRST 1 ROWS ONLY

Full specs: 
http://help.filemaker.com/app/answers/detail/a_id/12061/~/specifying-offset-and-fetch-first-clauses-in-filemaker-pro-using-the-executesql
