--- In firebird-support@yahoogroups.com, "Arno Brinkman"
<firebird@a...> wrote:

> Hi,
>
> > I have a table with 3 columns A, B, C like:
> > A, B, C
> > =======
> > 1, 1, A
> > 1, 2, A
> > 1, 3, B
> > 1, 4, A
> > 2, 10, A
> > 2, 11, B
> > 2, 12, C
> > 2, 13, A
> > ...
> >
> > Is it possible to write query, what returns:
> > 1) values in column A where "value in column c, corresponding to
> > _group_minimum_ value in column B" equals A => should return

1,1,A;

> > 2,10,A etc.
> > 2) as above but comparing max values - should return 1,4,A;

2,13,A;

> > etc.
>
> SELECT
> t1.A
> MIN(t1.B),
> (SELECT t2.C FROM ATable t2 WHERE t2.A = t1.A and t2.B = MIN

(t1.B)),

> MAX(t1.B),
> (SELECT t2.C FROM ATable t2 WHERE t2.A = t1.A and t2.B = MAX

(t1.B))

> FROM
> ATable t1
> GROUP BY
> t1.A
>
> This is only possible if A and B together are unique, because the

sub-select

> should be a singleton select. If not you can use MAX/MIN on t2.C,

but that

> depends on which record you want.
>
>
> Regards,
> Arno Brinkman
> ABVisie

Hi Arno!

Probably my example was too short and confusing :(
Your solution gives me _all_ values corresponding to min/max - but I
need only those t1.A values, where t2.C = "A". This is actually the
part I don't understand - maybe it is impossible with one query?

Still - I didn't expect this kind of query to be so quick, so I will
try your query inside SP and compare t2.C values with if() clause.

thanks,
Harri