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