cornievs wrote:

> Is there a way I can speed up the following query?
>
> Select CODE, STKCODE, DESCRIPTION from COUNT_SHEETS_DETAIL where code STARTING WITH 'TPL' and STKCODE in (Select STKCODE from (Select STKCODE, COUNT(STKCODE) as COUNT1 from COUNT_SHEETS_DETAIL where code STARTING WITH 'TPL' GROUP BY STKCODE order by STKCODE ) where COUNT1 > 1) order by STKCODE

First of all, you can remove inner ORDER BY, it only slows things down.

Here are some faster queries:

1. Assuming different CODE for the same resulting STKCODE:

select code, stkcode, description
from count_sheets_detail d1
where code starting with 'TPL'
and exists (
select 1
from count_sheets_detail d2
where d1.stkcode = d2.stkcode
and d2.code starting with 'TPL'
and d1.code <> d2.code )
order by 2;


2. If you use Firebird 2.1 or above:

select CODE, STKCODE, DESCRIPTION
from count_sheets_detail d1
join (
select STKCODE, COUNT(STKCODE) as COUNT1
from COUNT_SHEETS_DETAIL
where code STARTING WITH 'TPL'
GROUP BY STKCODE
)
order by 2

HTH

--
Milan Babuskov

==================================
The easiest way to import XML, CSV
and textual files into Firebird:
http://www.guacosoft.com/xmlwizard
==================================