On 17 Jan 2003 15:12:58 -0500, Greg Stark <[EMAIL PROTECTED]> 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 AS lowest_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. Servus Manfred ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly