Re: select a where min(b)

From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Tue, 30 Mar 2010 19:10:25 +0200
Message-ID: <81epk2F8tsU1_at_mid.individual.net>

On 03/30/2010 04:45 PM, magicwand wrote:

> But I still think, the solution of the OP is more efficient.

Frankly, I'd also stick with the original solution just because it is easy to understand, rock solid and likely performs better because since it's so basic the optimizer is likely well tuned for this type of query.

For the fun of it here are some more variants:

• join with inline view select a from step join ( select min(b) mb from step ) mini on step.b = mini.mb
• subquery factoring clause with mini as ( select min(b) mb from step ) select a from step join mini on step.b = mini.mb

(From memory since I don't have a DB handy right now.)

Of course there should be an index on B since it will make the min as well as the joins faster (sufficient amount of data assumed). If values are repetitive as shown in the original post then index compression should be considered, too.

Kind regards

        robert

-- 
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

Received on Tue Mar 30 2010 - 12:10:25 CDT