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 ==================================
