--- In firebird-support@yahoogroups.com, "Michael Fung" 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.
>
>
> TIA,
> Michael

Why not simply

select supplier, quoted_price, quote_date
from price_list p1
where not exists(select * from price_list p2
where p2.supplier = p1.supplier
and p2.quote_date > p1.quote_date)

NOT EXISTS is normally pretty quick.

If there can be several prices quoted by the same supplier on the same date, then you have to add another criteria - e.g. only accept the last PK.

Set