guido.klapperich@... wrote:

>
> I have a customers-table with the two columns nrfirst and nrlast, both
> are Integer. I have two indices
> CREATE UNIQUE INDEX I_CSNR_A_U ON CUSTOMERS (NRFIRST,NRLAST);
> CREATE DESCENDING UNIQUE INDEX I_CSNR_D_U ON CUSTOMERS (NRFIRST,NRLAST);
>
> With the following SQL-Statement 'select * from customers order by
> nrfirst,nrlast', I get the plan
> PLAN (CUSTOMERS ORDER I_CSNR_A_U)
> but when I have 'select * from customers order by nrfirst,nrlast desc',
> I get the plan
> PLAN SORT ((CUSTOMERS NATURAL))
> Can anybody explain to me, why not the index I_CSNR_D_U is used ?
>

What happens when you specify 'select * from customers order by nrfirst desc
,nrlast '?

And out of interest - Why do you want to index your customers by their first
names and then their lastnames? Are they all your friends? (g,d,&r).

So, change the index order to nrlast, nrfirst and in your order by clause
specify desc after the first column in the index.

'select * from customers order by nrlast desc, nrfirst'

will give you the right plan and the right result.

Paul
--

Paul Reeves
http://www.ibphoenix.com
taking InterBase further