selecting a column according to a minimum

[Agoston Bejo]

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.)

[Serge Rielau]

Re: selecting a column according to a minimum

SELECT i FROM
(SELECT MIN(j) OVER() as minj, j , i FROM T
WHERE [condition]) AS X WHERE minj = j

Whether that is faster or slower than your original depends on the
Oracle's internals which I don't know.

Cheers
Serge

[Turkbear]

Re: selecting a column according to a minimum

"Agoston Bejo" <gusz1@freemail .huwrote:

>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;

[Turkbear]

Re: selecting a column according to a minimum - Correction

Turkbear <john.g@dot.spa mfree.comwrote:

>"Agoston Bejo" <gusz1@freemail .huwrote:
>

>>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...