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
