At 09:08 AM 18/09/2003 +0000, you wrote:

>Dear all,
>
>I am sorry that my brain seems stuck with the following situation:
>
>I have the following table to hold supplier price quotes:
>
> PK SUPPLIER PRODUCT QUOTED_PRICE QUOTE_DATE
>===== =========== ======== ============= ===========
>
>75400 FRUITKING APPLE 2.00 2003-09-01
>75500 GMASTER APPLE 1.8 2003-09-04
>75600 QFOOD APPLE 2.5 2003-09-03
>75700 QFOOD APPLE 1.3 2003-09-08
>75800 QFOOD APPLE 1.8 2003-09-16
>75900 GMASTER APPLE 0.9 2003-09-10
>76000 GMASTER APPLE 0.7 2003-09-18
>76100 FRUITKING APPLE 0.5 2003-09-17
>
>
>
>Now I need to know latest quoted price from all suppliers, the result
>should look like this:
>
>SUPPLIER LAST_QUOTED_PRICE LAST_QUOTE_DATE
>=========== ============= ===========
>GMASTER 0.7 2003-09-18
>FRUITKING 0.5 2003-09-17
>QFOOD 1.8 2003-09-16
>
>
>I know it can be done using two SQL statement in my program. But is
>it possible to write *JUST ONE* SQL statement to complete the task? I
>tried subselect and group by but failed.

Will this do?

select
a.supplier,
a.last_quoted_price,
a.last_quote_date
from pricelist a
where a.last_quote_date = (
select max(b.last_quote_date) from pricelist b
where b.supplier = a.supplier
and b.product = a.product)
where a.product = :product

A stored proc would likely be faster than using the correlated subquery,
though:

create procedure latest_prices (product varchar(n))
returns (
lp_supplier varchar(n),
lp_last_quoted_price numeric(9,2),
lp_last_quoted_date date)
as
declare variable last_supplier varchar(n);
begin
last_supplier = '';
for select supplier, last_quoted_price, last_quoted_date
from pricelist
where product = :product
order by last_quoted_date desc
into :lp_supplier, :lp_last_quoted_price, :lp_last_quoted_date
do
begin
if (lp_supplier <> last_supplier) then
suspend;
last_supplier = :lp_supplier;
end
end

heLen