This is an old revision of the document!
Overview | Example | SQL | Optimization | Tips
The SQL behind AutoComplete is a simple query requesting grouped counts. For QuickText indexes, it is necessary to use an optimization setting so that one value is returned for each parsed word, rather than one value for each row.
The following SQL statement provides the results shown in the previous example:
SELECT FNAME, COUNT(*) FROM LIST WHERE FNAME='Wi*' GROUP BY FNAME ORDER BY 2 DESC WITH optimization=distinct_key
This SQL statement will provide results that can then be placed into drop-down boxes as needed:
> SELECT FNAME, COUNT(*) >> FROM LIST >> WHERE FNAME='Wi*' >> GROUP BY FNAME >> ORDER BY 2 DESC >> WITH optimization=distinct_k FNAME COUNT('*') ------------------------ ---------- WILLIAM 118130 WILLIE 19307 WILMA 5075 WILLARD 2350 WILLIS 1661 WILBUR 1660 WINIFRED 1365 WILBERT 1357 WILSON 1351 WILFRED 1141 WINSTON 946 WILL 826 WILLA 800 WINNIE 800 WILFREDO 698 WILLIAMS 626 WILEY 536 WILDA 457 WINFRED 445 WILFORD 428 WILMER 367 WINONA 366 WILHELMINA 348 WILBURN 345 WILTON 280 WILLY 216 WINNIFRED 212 WINDY 201 WINFORD 176 WILBER 175 WILLENE 163 WILLIAN 155 WILLIA 113 WILLETTE 104 WILLIEMAE 59 WINTER 58 WILLODEAN 48 WILLENA 47 WILHEMINA 43 WILLOW 39 WILLETTA 35 41 ROWS returned
See also: