--- In firebird-support@yahoogroups.com, "arhampshire"
<arhampshire@y...> wrote:

>
> --- In firebird-support@yahoogroups.com, "Dmitry Yemanov"
> <dimitr@u...> wrote:
> >
> > "arhampshire" <arhampshire@y...> wrote:
> > >
> > > select A, min(B), max(B), count(*)
> > > from myTable
> > > where A = 10
> > > group by A;
> >
> > Do you use some query generating tool or is it just a bad example?
> I see no
> > reason to use a GROUP BY clause here.
> >
> > > Are there any tips, or pointers to improve performance of
> queries?
> >
> > Could you please show a plan for this query?
> >
> >
> > Dmitry
> >
> I'd be very interested to know how you can write an aggregate query
> without the group by clause.

I think Dmitry was refering to the fact that you can only get one
record from the row in the query you use, so perhaps

select 10, min(B), max(B), count(*)
from myTable
where A = 10

Would be the same?

The more pertinent question is what plan is Firebird using to perform
the query? If you have an index on A,B and A,B desc then the mins and
max could be calculated pretty quickly. The count(*) may be an issue
though. It is only solvable using a table scan, so if it is already
doing a table scan, it may not be worth using any index to get the min
and max of B.

An alternative here is to use triggers to maintain this information in
a separate table.

Adam