Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: selecting a column according to a minimum - Correction
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... Received on Mon Oct 18 2004 - 08:49:31 CDT