Do you have time comparisons between this method and native FM find & sort? a large data set is going to have speed issue, regardless.
A work around may be to skip the ORDER BY, create a virtual list table and then Sort that with the summary.
Also, if this is the exact query, there are a number of errors that would prohibit it even working at all.
1. 'date' is a reserved word and must be escaped
2. GROUP BY must have all fields that are not aggregates.
GROUP BY category, \"date\"
But I doubt you want that, so remove date from the SELECT list.
Can you post the exact query or does that simply not matter because you have a large data set?
Thank you for your reply and pointing out.
Of course, I tried native FM find & sort and I know sorting a large data set has speed issue.
But I didn't mean it to sound like that.
I actually want that sorted records from only found set.
So, if I get it with native FM find & sort, I will constrain by "Perform find" and sort for the found set.
It works very fast.
But when I do same thing by ExecuteSQL, it seeme that it sorts for whole records in spite of constraining by WHERE.
I think that ExecuteSQL function should sort after constrain.
I'm afraid my expression may be hard to read, because I'm not so good at English.
Do you understand?
1 of 1 people found this helpful
I agree with you. I just wondered if you did a time comparison.
I also would like to see ExecuteSQL() improved.
There may already be Ideas posted to do that. https://community.filemaker.com/community/discussions/product-ideas
If so, please vote on them!
reminder: ExecuteSQL() does not work on Found Sets.