Thanks Helen and Milan,

Helen, your solution seems better. How come you are so quick while I
wasted the entire morning on it, I am a little bit depressed...

Thanks again,
Michael



--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
wrote:

> 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