Thanks for the help Set
Regards Cao

--- In firebird-support@yahoogroups.com, Svein Erling Tysvaer
<svein.erling.tysvaer@...> wrote:

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