Re: selecting a column according to a minimum - Correction

From: Brian Dick <bdick_at_cox.net>
Date: Mon, 18 Oct 2004 12:59:35 -0400
Message-ID: <1rsqiln0w2q46.193qb4tbji5pc.dlg@40tude.net>

On Mon, 18 Oct 2004 08:49:31 -0500, Turkbear wrote:

> Turkbear <john.g_at_dot.spamfree.com> wrote:
> 

>>"Agoston Bejo" <gusz1_at_freemail.hu> wrote:
>>
>>>Take a look at the following example:
>>>
>>>table T(i INTEGER, j INTEGER)
>>>
>>>I want to get the value of i where j is minimal and some conditions apply.
>>>
>>>(1)
>>>SELECT i FROM T
>>>WHERE [condition]
>>>AND j
>>>IN (SELECT min(j) FROM T WHERE [condition])
>>>
>>>The best would be if such a syntax was allowed (which it isn't):
>>>SELECT i, min(j) FROM T WHERE [condition]
>>>
>>>How can I rewrite the SELECT statement in (1) such that [condition] doesn't
>>>have to be typed in twice? Or how can I achieve the same thing in some other
>>>way? (I am open to other solutions as well as long as they are inside the
>>>limits of Oracle PL/SQL.)
>>>
>>Will this do what you want?
>>
>>Select i.min(j) from T
>>where [condition].
>>GROUP BY i;
>>
>>
>>

> No it won't I now realize..It will return all i  and, for each i, the Min(j) - the OP wants only those i that have as j the
> min(j).
>  value.
> 
> Sorry...

Something like

select i
from (select i

      ,      j
      ,      min(j) over (order by i
                          rows between unbounded preceding
                               and     unbounded following) min_j
      from T
      where [condition])

where j = min_j Received on Mon Oct 18 2004 - 11:59:35 CDT