Right, my suggestion was incomplete:

with C_INT_ALL(C_INT)
as (select first 10 C_INT
from CSV_DATA_TEST2
order by C_INT desc),
C_INT_UNIQUE(C_INT)
as (select distinct C_INT
from C_INT_ALL),
C_INT_LAST(C_INT)
as (select min(C_INT)
from C_INT_ALL),
C_CSV_MANY(ORDERCOL, C_INT, C_FLOAT)
As (select case when T3.C_INT is null then 0 else 1 end, T2.C_INT, T2.C_FLOAT
from CSV_DATA_TEST2 T1
join C_INT_UNIQUE T2 on T1.C_INT = T2.C_INT
left join C_INT_LAST T3 on T1.C_INT = T3.C_INT)

select FIRST 10 T1.ORDERCOL, T2.*
from C_CSV_MANY T1
JOIN CSV_DATA_TEST2 T2
on T1.C_INT = T2.C_INT
and T1.C_FLOAT = T2.C_FLOAT
order by ORDERCOL, T2.C_FLOAT

The thought behind this, is that the last row of the C_INT_ALL subselect will contain a value that may not all be in among the selected in a theoretical C_INT DESC, C_FLOAT ASC ordering, whereas all with higher values will be amongst the top 10 when C_FLOAT is added to the equation. This row is selected in the C_INT_LAST subselect and those that have this value will be given a higher value in ORDERCOL in C_CSV_MANY. Though, I must admit this is a complex way to try to speed up a query, how does it perform and does the result seem correct?

Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Martijn Tonies
Sent: 9. oktober 2009 11:30
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Re: How to mix ascending and descending fields in one index

Hello Set,

> Even if Martijns suggestion was wrong, maybe it could be part of a good
> solution?
>
> with C_INT_ALL(C_INT)
> as (select first 10 C_INT

C_INT_ALL only selects the first 10? That's back to square one.

> from CSV_DATA_TEST2
> order by C_INT desc),

> C_INT_UNIQUE(C_INT)
> as (select distinct C_INT
> from C_INT_ALL)



> select first 10 T1.*
> from CSV_DATA_TEST2 T1
> join C_INT_UNIQUE T2 on T1.C_INT = T2.C_INT
> order by T1.c_float asc

Given that C_INT is not unique, won't the JOIN with "CSV_DATA_TEST2" just
pick a random row?

> This will, of course, not work on Firebird 1.5 or older versions. I assume
> the speed will vary depending on the selectivity of C_INT and C_FLOAT.
>

With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com