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