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

> Do you meant where the corresponding lower t2.C to the group

is "A", then

> this is maybe the solution :
>
> SELECT
> t1.A,
> MIN(t1.B),
> (SELECT t2.C FROM ATable t2 WHERE t2.A = t1.A and
> t2.B = MIN(t1.B))
> FROM
> ATable t1
> GROUP BY
> t1.A
> HAVING
> (SELECT t2.C FROM ATable t2 WHERE t2.A = t1.A and
> t2.B = MIN(t1.B)) = 'A'
>
> SELECT
> t1.A,
> 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
> HAVING
> (SELECT t2.C FROM ATable t2 WHERE t2.A = t1.A and
> t2.B = MAX(t1.B)) = 'A'
>
> Although this could indeed be expensive depending on the indexes

you have

> and a SP may be much cheaper. With UNION ALL you can also combind

both

> queries together.
>
> Regards,
> Arno Brinkman
> ABVisie

Thanks!

Somehow I thought, that subquery with aggregate function inside the
having clause would be very slow - but it was only about twice
slower as SP, so I'm happy with it.

best regards,
Harri