Subject Re: MDX tags and sql builder From Mervyn Bick <invalid@invalid.invalid> Date Tue, 3 Dec 2024 11:56:49 +0200 Newsgroups dbase.getting-started On 2024/12/03 10:00, Rouke Bakker wrote: > We can create two indexes (or tags) using: > > index on surname tag surname > > and > > index on surname tage surname1 unique > > but how do I specify that the SQL builder uses surname1 instead of surname? > > In other words, does the SQL builder use the MDX tags or new indexes specified in the SQL builder? If the SQL builder creates its own new indexes, how would I specify something like 'index on surname tag surname unique'? > > Regards, > > Rouke The SQL builder doesn't use the .MDX file at all.  You have to set your index fields manually when you build a .sql file. You are going to have to build two separate .sql files with different names. The first one is quite straightforward.  In the SQL builder set the SortType to Ascending for the field surname and the SortOrder to 1.  The SQL builder will add the necessary code to the .sql file.  If you want to order on more than one field set the SortOrder value for each field accordingly. For the second .sql file do exactly the same.  After you have saved the .sql file open it in the Sourcecode editor and add DISTINCT immediately after SELECT. I use Firebird to store data and by creating a stored procedure I can use a parameter with the query's requery() method to return either the full ordered rowset or the distinct ordered rowset.  With .dbf files you will need to disable the query, change the .sql file and then make the query active again. Mervyn.