selecting a column according to a minimum

From: Agoston Bejo <gusz1_at_freemail.hu>
Date: Mon, 18 Oct 2004 13:50:21 +0200
Message-ID: <cl0atr$9n7$1@news.caesar.elte.hu>

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.) Received on Mon Oct 18 2004 - 06:50:21 CDT