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