You should be able to solve this by combining the SQL requests:
SELECT field FROM table WHERE date = (SELECT MAX(date) FROM table)
Does that really work? Others have reported that they can't "nest" one SELECT query inside another and get it to work with ExecuteSQL
Hmm. Well, I myself try to stay away from v12 as far as possible, so I haven't tested it with v12 ExecuteSQL, but I've run it via plug-in interface in v11 and it did work. I've tested both the old and the new SQL functions and both gave the same result. The sample above has one glitch, however: the "date" must be in quotes. Here's what worked for me:
SELECT number FROM test WHERE "date" = (SELECT MAX("date") FROM test)
I test the solution proposed by Mikhail and it works (without quotes), except, it is 10% slower.
In any case, my point was about the different dates’ format in the same statement (inside ExecuteSQL or the returned data).
Interesting as that contradicts what others have posted. Makes me wonder why it didn't work for them when it works here.
The data returned by ExecuteSQL is text (and SQL expects/produces a different format than FIleMaker dates entered via a FIleMaker layout). To work around this, you'll need to use a calculation to extract the month, day and year values in order to "recast" them as a FIleMaker date.
It's FileMaker Inc's call as to whether or not this is considered a "bug" or not...