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