Bruce, no problem. The ID list to be made unique can come from any table(s) in your solution, by any method. Recursive function, GetNthRecord, copy from a layout with one field, etc.
BLANK is a new table within FileMaker that has one record (fields are optional.) The only reason to have a BLANK table to to have a table to reference, which is not used to solve the problem directly *except* for the need to have a table listed in SQL code. You could use any other table in your solution, but having one record is important.
In my case, my customer is searching for a group of records, grouping by any number of options, and sometime need to know the number of non-zeros in all returned columns. For example (see image), they may have searched for PALM BAY IMPRTS, and grouped by Class. Within each of the 6 resulting classes, there are possibly 1,000s of stores. The STORE_KEY can be duplicated throughout each group and between groups.
My goal was to find the number of unique stores who stocked within the grouping, within each range. There are typically 6 columns that require a count, and a store can count as a placement if they have a positive sale (there are negatives) within the timeframe.
A large-selling store can be listed several times in all groupings. I cannot use the "self-join" method to solve the problem. I'm going to test the reciprocal method, but I'm not expecting fast results (I could be wrong).
What I have done so far, which has acceptable speed [server, remote FMP guests, reporting on between 500-5000 rows on average], is create a simple custom function with tail recursion that pieces together the valid STORE_KEYs within each group, each column. The resulting variable is $$sql in my above example, and I then simply use ValueCount ( ExecuteSQL ( $$sql ; "" ; "" ) ) to get the count of unique results.
What I am doing is bypassing the need for a custom UniqueValues function. What I would LOVE to have is some native SQL speed of "select count(distinct(store_key))" with a crafted WHERE clause, but that's not about to happen.
Bruce, if you know of the fastest way to get unique values, then I'd appreciate your expertise in this area. I know you're a speed guru, and always appreciate reading about your techniques.
I don't know about faster, or large record sets, but I now see that your technique does work and I see what you're trying to do.
Assuming you had collected the list of items into variable $list then:
If[ valueCount( $list ) > 1]
Set variable [$q ; "SELECT " & Substitute($list ; "¶"; " FROM BLANK UNION¶SELECT " ) & " FROM BLANK" ) ]
Set variable [ $result ; ExecuteSQL( $q; "" ; "" ) ]
It doesn't appear to me that you have to use a BLANK (single record) table though. It works OK with any standard table occurrence.
It only returns rows equal to the record count if you have only a single item in the list.
You definitely want to use a table one record, or at least a very sparse table. There is more going on under the hood that relates to the record count in the one table referenced than just resolving a single list item.
1 record = 2 seconds
10 = 4
50 = 10
100 = 20 seconds
A reference a large table (>10,000) is taking many minutes... too long for me to bother timing and one processor is pegged.
It also appears to me that all the data pairs must be integers, the way it is written right now.
Bruce, you are correct. I had the luxury of having numeric key fields for what I have been working on so far, so for simplicity of example I ignored them. Single quotes work.
If you use text for one column, then all items in that column must be text. Removing quotes from any of the second pairs resolves to "?".
select 12979193,'a' from BLANK UNION
select 12979193,'2' from BLANK UNION
select 12979193,'2' from BLANK UNION
select 12979193,'3' from BLANK UNION
select 12979193,'2' from BLANK
Of note, it appears that you cannot use INTERSECT, EXCEPT, or MINUS within a ExecuteSQL in place of the UNION operator. Of course, I could be entering something wrong, but even the simplest examples are failing for me. Bummer.
It took me a second to get what you are doing, but now that I get it, I like it.
I have a good deal of appreciation for the sort of thinking that goes into these types of techniques -- thank you for sharing.
Well, d'oh (said to myself)! Of course it helps to report on a table with one record. Without a WHERE clause, a constant will be for all records of the table. Using UNION will have to perform much more work when there is so mch duplication. I believe you would need a WHERE clause for every SELECT (I can't get parens to work) so it's easier (and faster) to use a BLANK table.
A single SELECT will return the number of records because there is no UNION used in the statement.
UNION (unless you include ALL) will return unique values. Calling a one record table makes an IMPLIED Cartesian JOIN between the constants and the BLANK table.
-- sent from my iPhone4 --
Interesting, but why is this better than UniqueValues()?
Perhaps you could explain.
What is UniqueValues()?
The use of SQL UNION on constants is typially much faster than the custom function, and does not run into the limitations of recursion (10,000 or 50,000 limits.)
I was toying with such a method because I'm looking for speed and reliability, and I could easily get the proper row IDs for each group. This solution is hosted on FMS, the data table containe 60M rows, and the clients can have crappy Internet connections, so I have to be careful of speed.
For small data sets (less than 10,000 rows), a complete SQL statement is fine and sometime very fast. But when you attempt to run on 60M rows, there's something slow going on that must be FileMaker's implementation of SQL and not the SQL itself.
For example, when I attempt to use the following, it takes hours to resolve in FileMaker, but seconds in SQLite.
where premise='C' and
Oddly enough, I resorted to one of the oldest methods -- sum of recipricals. No custom functions, no recursion, no executeSQL, but a few extra fields per column in question. Sad in a way, but the only way that works well with what I am doing.