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

Reply via email to