> I can't tell him how to do it but I think that what he actually

wants is to

> group by Cod_art returning
>
> the minimum order cost in this area and also the order ID

associated with

> the minimum cost.
>
> In the example you give, it will be order_id 1 because it has the

minimum

> cost, 10 as opposed to 15,
>
> however if there are many with the same value, the whole idea makes

less

> sense.

Agreed. But there is no way of telling Firebird that the order_id you
are interested in is the one that corresponds to the minimum cost and
as you correctly point out, it would also need to know how to cope
with multiple orders with the same cod_art and the same cost (which
was also the minimum)

Assuming you do not care which of the orders you returned that
contained the minimum cost, the way with the best performance to
achieve that would be a selectable stored procedure.

set term ^ ;

create procedure blah
returns
(
order_id integer,
cod_art integer,
cost integer
)
as
begin
for SELECT cod_art, MIN(cost)
FROM orders
GROUP BY cod_art
into :cod_art, :cost
do
begin
select first 1 order_id
from orders
where cod_art = :cod_art
and cost = :cost
into :order_id;
suspend;
end
end
^

SET TERM ; ^

If you wanted the minimum or maximum order then you could replace
first 1 with min(order_id) etc, but if you do not care then the first
1 will be quicker.

I imagine an index on orders(cod_art, cost) would be extremely useful
in both queries.

CREATE INDEX IX_BLAH ON ORDERS(COD_ART, COST);

If there are a lot of order records with the same cod_art and cost
entries, then you could put an index on orders(cod_art, cost,
order_id) which is a good alternative.

To call it

select *
from blah;

Adam

>
>
>
>
>
> --- In firebird-support@yahoogroups.com, "Alberto Pesce"
> <palberto@> wrote:
> >
> > I need to make this query:
> > the lower cost of product ordered for each oreder.
> >
> > this work well
> >
> > SELECT cod_art, MIN(cost)
> > FROM orders
> > GROUP BY cod_art
> >
> > but this return each row in orders
> >
> > SELECT order_ID, cod_art, MIN(cost)
> > FROM orders
> > GROUP BY order_ID, cod_art
> >
> > How can I add order_ID in the first query?
>
> Imagine you had the following table
>
> Orders
> ======
>
> order_ID Cod_art Cost
> -------- ------- ----
> 1 1 10
> 2 2 20
> 3 1 15
> 4 3 20
> 5 3 22
>
> Your first query would return
>
> Cod_art MinCost
> ------- --------
> 1 10
> 2 20
> 3 20
>
> Now Cod_art 1 could be order_id 1 OR 3??
> Cod_art 2 will be 2
>
> How could you possibly tell which should go first? You cant.
> Alexandre's second query is probably the one you want.
>
> Adam