I believe that the data table and the field for the where statement have to be fully qualified field names (Table::Field ). To help me develop these I have been using SeedCode's SQLExplorer.
Anyway, I think your query should look something like this...
FROM \"RACE_DATA\" a
1 = ROW_NUMBER() OVER (PARTITION BY a.a_kf_countyIdKey ORDER BY a.a__kp_idKey DESC);
WHERE a.\"a_kf_raceIdKey\" = ?
; ""; "" ; RACE_DATA::a__kp_idKey)
Another resource is The missing FM 12 ExecuteSQL Reference by Beverly Voth. You can get this here: http://www.filemakerhacks.com/?p=6406
I'm still working through this and new to SQL. But have found both of these to be very helpful. See the section on Case statements, it may help you solve the Qualify issue Nick points out.
Message was edited by: Bruce Herbach
You're correct in that, AFAIK, the Qualify statement is not supported in FMP. Nor is the Partition by statement. You'll need to re-write to get rid of those bits.
Bruce is correct that it's best practice to fully qualify your fields, but it's not a necessity if querying only one table.
Finally, you may want to find and download the SQLdebug custom function; it helps by letting the developer know what part of the query is having problems.
Thanks Nick and Bruce, I actually found this guide to be helpful:
It contains a list of the reserved SQL words that filemaker uses, which can point me in the direction of where I need to go.
As an FYI, even though "FIRST" is a reserved word, I could not get it to work in playing around, even with the fully qualified field names.
ExecuteSQL ( "
FROM RACE_DATA r
WHERE r.a_kf_raceIdKey = ?
ORDER BY r.a__kp_idKey DESC
GROUP BY r.a_kf_countyIdKey
" ; "" ; "" ; RACE::a__kp_idKey )
Did you try Group by .... then Order by... ?
Yes, ORDER BY needs to be the last clause in the SELECT statement.
I've tried it without ORDER BY at all and am still not able to get anything returned.
At this point I am imagining SELECT FIRST is not supported even though the name is reserved.
Correct; First is not supported in FM's SQL.
The list or reserved words is for SQL; it has no bearing on whether it's supported in FileMaker.
So is anyone aware of an ExequteSQL statement that will query, group by, order by and return the first row of each group? Everything I read up on returning the first row of a group in SQL is not supported by filemaker (IE TOP, FIRST, QUALIFY, etc..)
I would figure this would be easy, but it's not?
In my tests, I found that FIRST(), LAST(), TOP, LIMIT, ROWNUM (ways to constrain the 'found set') do NOT work with ExecuteSQL function.
If you can narrow your found records, you can use GetValue ( sql_result; 1 ), if the row-delimiter is the return- char(13)
This would only work for me if I could return data from executeSQL in an array. My goal was to use the calculation in a chart, hence the need to have a list returned by the function (of the latest value, by group)
It might just not be possible, in which case the backup plan is to script it. I had rather not wanted to do this due to not wanting it to be tax heavy on the system, (compiling data from ~100 races and it needs to update per-minute).
Hopefully I can optimize the scripting if it's not possible via ExecuteSQL.
what is the top row of each group? the max the min the alpha - need more info
Sorry, it was in my original post up top.
I am grabbing a numeric count to use in a chart from the top row. The top row becomes the top row by sorting DESC from a serial number. The data also needs to be grouped by a countyID and candidateID.
The return should be the sum of the most recent count values from each county related to a queried raceID.
WHERE a_kf_raceIdKey = 1 AND
a_kf_countyIdKey = (
WHERE a_kf_raceIdKey = 1 )
This only returns a sum of the highest county's values (Since there are 88 counties, this will always return a total from county 88's entries). I need a return delimited list of the most recent entry from each county.
From the feedback above showing that there is no way to filter a first row out of a set of returned/grouped records, I have already tried a different approach of scripting to build a global variable, using ExecuteSQL inside the loop to speed it up.