What about

SELECT distinct aa.AREA_ID
FROM
TMP_CKRAJ aa
WHERE
EXISTS(SELECT 1 FROM TMP_CKRAJ tc WHERE
tc.ID_CCOUNTRY = 'CZE' and
tc.NAME_TYPE = 'B' and
tc.AREA_ID = aa.AREA_ID and
tc.rdb$db_key > aa.rdb$db_key)

I just guess that's similar to what Arno intended to write. Without the GROUP BY you'll either need the distinct or add a further NOT EXISTS (distinct is simpler).

Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Milan Tomeš - Position
Sent: 26. mars 2010 10:56
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Index suggestion

Hi,

Dne 26.03.2010 10:39, Arno Brinkman napsal(a):

> Hi,
>
>
>> SELECT DISTINCT AREA_ID
>> FROM TMP_CKRAJ
>> WHERE
>> (ID_CCOUNTRY = 'CZE') AND
>> (NAME_TYPE = 'B')
>> GROUP BY AREA_ID
>> HAVING
>> (COUNT(AREA_ID)> 1)
>>
>> It's easy to make an index for ID_CCOUNTRY and NAME_TYPE, but is there
>> any chance to help engine with index that can be used for DISTINCT/GROUP
>> BY/HAVING COUNT ?
>>
> Compound index on (ID_CCOUNTRY, NAME_TYPE) looks most usefull to me, but i don't see the usage of DISTINCT in your query
> here.
>

You're right. There is no reason to use DISTINCT in that query. Multiple
AREA_IDs are eliminated by GROUP BY clause.

> Also think of the next possible approach for your query:
>
> SELECT
> aa.AREA_ID
> FROM
> Table_All_Area aa
> WHERE
> EXISTS(SELECT 1 FROM TMP_CKRAJ tc WHERE
> tc.ID_CCOUNTRY = 'CZE' and
> tc.NAME_TYPE = 'B' and
> tc.AREA_ID = aa.AREA_ID)
>
>

I guess this will not work. This will select all AREA_IDs that exists,
but I need only AREA_IDs that exists more than once (in next step I'm
updating NAME_TYPE to value 'E' to get only one record for every
condition AREA_ID = xxx and NAME_TYPE = 'B'). I don't have a table with
distinct values of AREA_IDs.