Bruno Wolff III <[EMAIL PROTECTED]> writes:

> On Fri, Jan 17, 2003 at 13:39:11 -0500,
>   Greg Stark <[EMAIL PROTECTED]> wrote:
> > 
> > So it would be possible to say for example:
> > 
> > select min(column1),lookup_min(column1,column2) from tab
> > 
> > to do the equivalent of:
> > 
> > select column1,column2 where column1=(select min(column1) from tab) limit 1

As several people have pointed out this example isn't sufficiently complex to
make rule out various other reasonably efficient SQL implementations.

If you're unconvinced that this function would be handy consider a more
complex query:

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. 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.

Currently to write this I think you would have to join against items_for_sale
twice, once to group by item_id and get the least price, then again to lookup
the store.

SELECT item_id, min(store_id) 
  FROM items_for_sale, (
       SELECT min(price) AS lowest_price 
         FROM items_for_sale
        WHERE item_category = ?
        GROUP BY item_id
       ) AS x
 WHERE items_for_sale.item_id = x.item_id 
   AND items_for_sale.price = x.lowest_price 


---------------------------(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