In Firebird 2, 'select from select' is possible, but your well-defined
problem is solvable without using any such construct, you just need a
minor change to what Michael suggested:

select distinct A.col_a, A.col_b from table_a A
where exists(select 1 from table_a B
where A.col_a = B.col_a
and A.col_b <> B.col_b)

HTH,
Set

mailgroupza wrote:

> Hi Michael
> Thanks for the reply but..
> I need the distinct result as there are thousands of records with
> the same combination of col_a, col_b
>
> your result returns if col_b has more than one occurance in the table
> I need the result if col_b returns more than one result from the
> distinct result
>
> This one is giving me a hard time
>
> would help if I could do something like select .... from (select ....)
>
>
>
> --- In firebird-support@yahoogroups.com, "mspencewasunavailable"
> <firebird@...> wrote:
>> --- In firebird-support@yahoogroups.com, "mailgroupza" <cao@>
>> wrote:
>>> select distinct col_a, col_b from table_a
>>> order by col_a, col_b
>>>
>>> returns something like
>>> col_a col_b
>>> ===== =====
>>> cola-a colb-1
>>> cola-b colb-1
>>> cola-c colb-2
>>> cola-c colb-3
>>> cola-c colb-4
>>> cola-d colb-5
>>> cola-e colb-1
>>> cola-e colb-2
>>> cola-f colb-1
>>> ...
>>>
>>> I want the query to only extract those results where col_a
>>> has more than 1 occurance
>>>
>>> ie the result should be ...
>>> col_a col_b
>>> ===== =====
>>> cola-c colb-2
>>> cola-c colb-3
>>> cola-c colb-4
>>> cola-e colb-1
>>> cola-e colb-2
>>>
>>> hope someone is able to point me in the right direction
>>>
>>> regards cao
>>>
>> As it happens, I have a database with a populated table which was
>> close enough in structure to try things. This worked for me (using
>> FB2).
>>
>> select A.col_a, A.col_b from table_a A
>> where exists(select 1 from table_a B
>> where A.col_a = B.col_a
>> having count(B.col_b) > 1 )
>>
>> I think having an index on col_a might be important if this table
>> is of any size, though.
>>
>> Michael D. Spence
>> Mockingbird Data Systems, Inc.