Re: Select f1, min(f2) ????

Konstantin Kivi <kivi_at_glas.apc.org> wrote:
>...
>I don't like the following because I think
>that it may be done more efficiently, because
>f1 is found at the time of subquery.
>
>select f1, f2 from t1 where f2=min( select f2 from t1
> where (<<criteria>>));
>
>For simplicity lets assume that all f2 are different.
>...

A little bit tricky:
Use a singleton select or, in PL/SQL: OPEN, 1*FETCH, CLOSE.

 SELECT f1, f2 FROM t1
 WHERE (<<criteria>>)
 ORDER BY f2;

This works very fine, if you have an index on f2.

Depending on your ORACLE version, may be you should add

 AND f2 BETWEEN <<lowvalue>> AND <<highvalue>>

in order to make the optimizer use the index.

Regards, Klaus

-- 
Klaus Eichler       | Haus 2, 3C7.1       | email ek2918_at_m30x.nbg.scn.de
Siemens AG, AUT 652 | Moorenbrunn         |
Postfach 4848       | Gleiwitzer Str. 555 | Tel   +49 911 895-2918
D-90327 Nuernberg   | D-90475 Nuernberg   | Fax   +49 911 895-4802