1 2 Previous Next 15 Replies Latest reply on Aug 27, 2013 2:58 PM by BruceRobertson

    Unique values or value pairs: ExecuteSQL function works nicely

      This is a method to produce unique lists, not gather the values. As typical, a sort and a summarized export are much faster, but I have a need for many sub-summarized unique counts in several columns, and the many exports that would be needed have their own problems.

       

      ValueCount ( ExecuteSQL ( $$sql ; "" ; "" ) )

       

      where $$sql is something like as follows:

       

      select 6651031 from BLANK UNION

      select 6651032 from BLANK UNION

      select 6651034 from BLANK UNION

      ...

      select 6651031 from BLANK UNION

      select 6651034 from BLANK

       

      BLANK is a FileMaker table that has one record. It appears that using an existing table with many rows slows things down and will cause problems for single selects. The UNION pieces the IDs together and by SQL definition the result is unique.

       

      20,000 records takes 1-1/2 minutes.

      10,000 = 30 seconds

      5,000 = 8 seconds

       

      There are over 2,500 unique results in my tests.

       

      So not fast for large sets, but tidy. No sorts, exports, custom recursive functions, etc. Also, you can add another value and find duplicate pairings:

       

      select 111,222 from blank union

      select 112,222 from blank union

      select 113,222 from blank union

      select 111,222 from blank union

      select 111,222 from blank union

      select 111,222 from blank union

      select 111,222 from blank union

      select 111,222 from blank union

      select 113,222 from blank union

      select 113,223 from blank union

      select 111,222 from blank union

      select 111,222 from blank union

      select 111,222 from blank union

      select 111,222 from blank

       

      Will result in 4 unique rows. Notes: "union" cannot end the SQL code. Also you need to have only one record in the table BLANK if you have only one line of value(s). "select 111,222 from blank" will give you 100 results if you have 100 records.

        1 2 Previous Next