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.

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

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

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://80.126.130.81