--- In firebird-support@yahoogroups.com, "Ann W. Harrison"
<aharrison@i...> wrote:

> Gary Benade wrote:
>
> >
> > ended up with this, based on your example
> >
> > select b.name, count(*) from customers c
> > join branches b on b.orderappend = c.branchcode and b.enabled

= 'T'

> > where c.weighted_value >=
> > (SELECT FIRST 1 SKIP 80000 Weighted_Value FROM Customers
> > ORDER BY Weighted_Value DESC)
> > group by 1
> >
>
> Do you have any idea what the performance of that is likely to be?
>
> Regards,
>
>
> Ann

Hi Gary,

Just a suggestion.

I would do this in a selectable stored procedure.

SELECT FIRST 1 SKIP 80000 Weighted_Value
FROM Customers
ORDER BY Weighted_Value DESC
INTO :Weighted_Value;

--Make sure you have a descending index on Weighted_Value. Once you --
--have this weighted value, your other query will run much faster.

for select b.name, count(*)
from customers c
join branches b on b.orderappend = c.branchcode and b.enabled = 'T'
where c.weighted_value >= :Weighted_Value;
group by b.name
into :Name, :Cnt
do
begin
suspend;
end

The problem with the other logic is that in FB, sub selects are
executed for every record, which in this case would result in a
massive overhead.

Adam