Re: Suggestion for aggregate function

On 17 Jan 2003 15:12:58 -0500, Greg Stark <gsstark@mit.edu> wrote:
>SELECT item.*, store.*, x.lowest_price
>  FROM item, store, (
>       SELECT item_id,
>              min(price) AS lowest_price, 
>              lookup_min(price,store_id) AS lowest_price_store
>         FROM items_for_sale
>        WHERE item_category = ?
>        GROUP BY item_id) AS x 
> WHERE item.item_id = x.item_id
>   AND store.store_id = x.store_id
>
>There's really no reason for the database to have to do more than one scan of
>items_for_sale with one nested_loops lookup of item and store.

Greg, we already have this feature, just the syntax is a bit different :-)

SELECT item.*, store.*, x.lowest_price FROM item, store, (      SELECT DISTINCT ON (item_id) item_id,             price
ASlowest_price,              store_id AS lowest_price_store        FROM items_for_sale       WHERE item_category = ?
  ORDER BY item_id, price) AS x WHERE item.item_id = x.item_id  AND store.store_id = x.lowest_price_store;
 

> Ideally if
>there's an index on items_for_sale on item_id, price it should be able to use
>it too, but that's unlikely.

ServusManfred