Re: Suggestion for aggregate function

On 17 Jan 2003 15:12:58 -0500, Greg Stark <gsstark(at)mit(dot)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 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