Can you post your Query attempt(s)?
Generally speaking: Criteria that specifies "largest", "first", "smallest"... Values that identify the desired record only relative to the other records in the table, is accomplished by using a sort of all records matching the other criteria used in your query, then selecting the first (or last) record from the sorted set or group. Exactly which of several methods that you might use to do that depends on layout design and query method.
Sure. I tried various SQL modes, and although the subqueries seemed to be the right path, they only work in Filemaker if they are in the WHERE part of the SQL code. Anyway, my original concept was this:
SELECT Name, City, max(Age), max(NumberOfDogs) FROM Table
The result set the query yields is this:
Name City Age NumberOfDogs
Rita Boston 26 3
John New York 42 5
Rita Sydney 30 1
John Boston 41 2
Unfortunately the NumberOfDogs columns displays inappropriate results, as John (NYC) has 2 dogs. However, if the MAX function is omitted from the SELECT clause, the code fails to execute because of the grouping.
Thx for any suggestion in advance,
Very true. Coming from MS Access I believe I would have tried the first() / last() functions, but they don't exist in Filemaker.
Actually my original tables are more complicated and I used this demonstrative example of one single table so that they are easy-to-digest and the pro people here could write a quick SQL or TO hint for me that I can later scale up and implement in my solution. As said, either of the two querying methods would be good for me.
We have equivalent methods--one of which uses a function named "last". Go to record/request/page [first,last...], GetValue, And Fetch First Row Only, all might be used to do that.
As I said before, it depends on your query method--something that you have not described yet in this discussion.
In general you can query data in FileMaker:
- By performing a find and then (if necessary) sorting the results
- By setting up a relationship to the data where the criteria control what records match. A portal or Go TO Related Records can be used to bring up a list of records satisfying the query criteria
- By using both a relationship and a filtered portal
- By using the ExecuteSQL function. (results can be listed in a single field or you can get a list of primary keys to use to quickly get a found set of such records.)
Thank you, more or less I am aware of the concepts you described.
Here I specifically would like to
a) group records by certain fields
b) choose the last record in the group and query its othe fileds (in my example: the number of dogs)
The functions you have described are either not for use in SQL (Go to record, GetValue) or serve a different purpose. FETCH unfortunately fetches the first row of the complete result set, not the first row of each group.
Any specific help on the original problem is appreciated,
I tried with your data and using the
clause. But that did not work either.
Sub-selects are not always helpful with ExecuteSQL.
Perhaps sorting and looping the list (scripted) will help you walk the rows and return want you want?
Just like BEV I was thinking that a scripted solution might be easiest...
Thanks a lot for your try! Literally a sub-query should do the job, but it should be referenced from the FROM clause, which the FM SQL engine does not allow.
Scripting could be one option, or – what I am currently working on – is two SQL commands subsequent to each other, where the first is a make table query (using the epSQLexecute plugin-feature).
Set up a self join
YourTable::FirstName = YourTable 2::FirstName AND
YourTable::City = YourTable 2::City
Double click the relationship line and sort YourTable 2 by Age in descending order.
On a layout based on YourTable, You can put any fields from YourTable 2, such as Dogs, to show data for the oldest person with the same name and city.
In a sub summary report where each group of records with the same name and city is condensed into a single row by setting up a sub summary layout part in place of the body, you'd get the table that you have shown in your initial post.
Just as a side comment. Experienced developers used to SQL based systems frequently encounter this particular bit of frustration. You know the result that you want, but when you reach into your tool box for that familiar SQL tool for getting what you want, you find that it won't work in FileMaker and you have to use other means to the same end.
In general, FileMaker's "Query by Example" design makes very simple queries very easy and user friendly at the cost of making more complex queries such as this one more complex and difficult than you would find in a true SQL based DB.
Thank you very much for your answer. Stupid me, I tried the self-join relationship, but only in SQL, where I faced the mentioned limitations.
Combining the TO self-join with the reporting view's grouping is a very nice solution. Am I correct, that such sub-summaries are also available in table view, therefore an Excel exportable table can also be produced this way?
Thanks again, have a nice day
You won't need table view to export this to excel. Just use the "group by" option to export one row of data for every group of records produced by your sorting.