From:  "Brian R. Bullock" <brian@wwwebweaver.com>
To:  miva-users@miva.com
Subject:  Retrieving only the unique field values
Date:  Mon, 19 Mar 2001 20:46:11 -0800

I needed to retrieve only the unique values stored in a 
database field. In my case it was unique country names 
but it could be anything.

In the past I've always used a while loop to step through 
a db checking the current record's field value against a
list of found values. The problem with that is it looks at 
EVERY record which can take a long time on large databases.

Ivo's discovery about speeding up MvFIND-MvFILTER combination 
searches by making the filter dynamic sparked an idea.

Why not use a dynamic filter to find the unique field values!
Here is what I came up with:

<MvASSIGN NAME="alias" VALUE="my_db">
<MvASSIGN NAME="filename" VALUE="path/to/my_db.dbf">
<MvASSIGN NAME="index" VALUE="path/to/my_index.mvx"> [ed: or VALUE="0" if no index]
<MvASSIGN NAME="fieldname" VALUE="d.country">
<MvASSIGN NAME="filter" VALUE="NOT d.deleted"> [ed: or VALUE="0" if no filter]
<MvEVALUATE EXPRESSION="{find_unique_values(alias,filename,index,fieldname,filter)}">
<MvEXIT>

<MvFUNCTION NAME="find_unique_values"
 PARAMETERS="pAlias,pFilename,pIndex,pFieldname,pFilter"
 STANDARDOUTPUTLEVEL="">
<MvIF EXPRESSION = "{pIndex}">
  <MvOPEN NAME="{pAlias}"
   DATABASE="{pFilename}"
   INDEXES = "{pIndex}">
<MvELSE>
  <MvOPEN NAME="{pAlias}"
   DATABASE="{pFilename}">
</MvIF>
<MvIF EXPRESSION = "{pFilter}">
  <MvASSIGN NAME = "pFilter" VALUE = "{'AND('$pFilter$')'}">
<MvELSE>
  <MvASSIGN NAME = "pFilter" VALUE = "">
</MvIF>
<MvFILTER FILTER="{(NOT('|'$&[pFieldname];$'|' IN l.tmp_list))&[pFilter];}">
<MvASSIGN NAME="l.tmp_list" VALUE="|">
<MvWHILE EXPRESSION="{NOT d.eof}">
  <MvASSIGN NAME="l.tmp_list" VALUE="{l.tmp_list$&[pfieldname];$'|'}">
  <MvSKIP>
</MvWHILE>
<MvCLOSE>
<MvFUNCTIONRETURN VALUE="{glosub(glosub(ltrim(rtrim(glosub(glosub(l.tmp_list,' ',asciichar(7)),'|',' '))),' ','|'),asciichar(7),' ')}">
</MvFUNCTION>

[ed: the above is a fancied up version of the function that appeared in the original post.]

This runs MUCH, MUCH faster than my old while loop.

I'm probably not the first to discover this but I 
don't remember anyone mentioning it so I decided 
to share.

Brian R. Bullock

[ed.] Of course to retrieve a sorted token list you
can use an index or, if the field isn't indexed,
you can use the sort_token() function in my token_lib.mv
library (http://wwwebweaver.com/func_junc/functions.html).