disabled_ScottKoontz

Unique values or value pairs: ExecuteSQL function works nicely

Discussion created by disabled_ScottKoontz on Aug 25, 2013
Latest reply on Aug 27, 2013 by BruceRobertson

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.

Outcomes