Michael Fung wrote:

> 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

You can try:

select supplier,
(select first 1 quoted_price from table t2
where t2.pk = t.pk and t2.supplier = t.supplier
order by quote_date desc),
max(quote_date)
from table t
group by supplier;

However, I'm not sure whether the order by will work correcly in
subselect, so if it doesn't you can try something like this:

select supplier,
(select first 1 quoted_price from table t2
where t2.pk = t.pk and t2.supplier = t.supplier
and quote_date = (select max(quote_date) from table t3
where t3.supplier = t2.supplier)
),
max(quote_date)
from table t
group by supplier;

This should work very slow, so if you have big tables, it's better to
write a stored procedure to do the job.

--
Milan Babuskov
http://fbexport.sourceforge.net