>So I have a table with close to a milion records and one of the fields called rcode char(6) contains codes, about 300 distinct values in the whole table.
>
>I need a way to get the list of unique values. It seems like neither of the following quesries are optimizable (according to sys(3054,11) there is no optmization used and the queries are slow)
>
>select distinct rcode from testtable
>select rcode from testtable group by rcode
>
>Any idea on how to get the list of unique values with an optimizeable query?
>
>thank you
>
>Sorin
Sorin,
Make sure cpcurrent() and cpdbf() matches. Then include a where clause on an existing index. ie: Assuming there is an index on upper(country)
SELECT distinct country FROM customer WHERE UPPER(Country)=''
However distinct by itself is slow. Having a unique index and copy to might produce faster results.
Cetin