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