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]

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]

"Agoston Bejo" <gu***@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;

[Turkbear]

Turkbear <jo****@dot.spa mfree.com> wrote:

"Agoston Bejo" <gu***@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...

[Agoston Bejo]

Actually, here is what I could come up with:

select i from
(SELECT i, j FROM T WHERE [condition] ORDER BY j )
WHERE ROWNUM = 1

Is this very inefficient?

"Agoston Bejo" <gu***@freemail .hu> wrote in message
news:cl******** **@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.)

[Martin Dachselt]

"Agoston Bejo" <gu***@freemail .hu> wrote in message news:<cl******* ***@news.caesar .elte.hu>...

Actually, here is what I could come up with:

select i from
(SELECT i, j FROM T WHERE [condition] ORDER BY j )
WHERE ROWNUM = 1

Is this very inefficient?

According to oracle doc the rownum is computed before the "order by" statement
is applied.

Perhaps better:

select i,j from
(select i,j,row_number( ) over (order by j) rn)
where rn=1;

Bye
Martin

[Holger Baer]

Martin Dachselt wrote:

"Agoston Bejo" <gu***@freemail .hu> wrote in message news:<cl******* ***@news.caesar .elte.hu>...

Actually, here is what I could come up with:

select i from
(SELECT i, j FROM T WHERE [condition] ORDER BY j )
WHERE ROWNUM = 1

Is this very inefficient?
According to oracle doc the rownum is computed before the "order by" statement
is applied.

Not if the order by is in a inline view:

baer@DEMO10G>cr eate table test (i integer, j integer);

Table created.

baer@DEMO10G>be gin
2 for x in 1..100 loop
3 insert into test values (x, 100-x);
4 end loop;
5 end;
6 /

PL/SQL procedure successfully completed.

baer@DEMO10G>co mmit;

Commit complete.

baer@DEMO10G>se lect i from (select i,j from test order by j)
2 where rownum=1;

I
----------
100

baer@DEMO10G>se lect min (j) from test;

MIN(J)
----------
0

baer@DEMO10G>se lect i from test where j=0;

I
----------
100


Perhaps better:

select i,j from
(select i,j,row_number( ) over (order by j) rn)
where rn=1;

Not without additional input for the optimizer:

baer@DEMO10G>se lect i,j from
2 (select i,j,row_number( ) over (order by j) rn from test)
3* where rn=1
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOS E (Cost=3 Card=100 Bytes=390
0)

1 0 VIEW (Cost=3 Card=100 Bytes=3900)
2 1 WINDOW (SORT PUSHED RANK) (Cost=3 Card=100 Bytes=500)
3 2 TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=2 Card=100
Bytes=500)


baer@DEMO10G>se lect i from (select i,j from test order by j) where rownum=1;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOS E (Cost=3 Card=1 Bytes=13)
1 0 COUNT (STOPKEY)
2 1 VIEW (Cost=3 Card=100 Bytes=1300)
3 2 SORT (ORDER BY STOPKEY) (Cost=3 Card=100 Bytes=500)
4 3 TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=2 Card=1
00 Bytes=500)


Note the appearence of STOPKEY: The optimizer nows that with rownum=XY
you will likely not take all of the result. This might lead the optimizer
to favour a first_rows plan.

Regards,

Holger

[Martin Dachselt]

>

baer@DEMO10G>se lect i from test where j=0;

I
----------
100
Yeah, you are right. Note the appearence of STOPKEY: The optimizer nows that with rownum=XY
you will likely not take all of the result. This might lead the optimizer
to favour a first_rows plan.

Strange: costs and execution time seems bo be equal, even for big
tables.
I thought the optimizer is guessing disk and cpu usage.

select * from (
select id,to_char(date time,'yyyy-mm-dd hh24:mi:ss') from fdetailrecord
order by id
) where rownum < 5;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOS E (Cost=82560 Card=4 Bytes=2
10937680)

1 0 COUNT (STOPKEY)
2 1 VIEW (Cost=82560 Card=8789070 Bytes=210937680 )
3 2 SORT (ORDER BY STOPKEY) (Cost=82560 Card=8789070 Bytes
=123046980)

4 3 TABLE ACCESS (FULL) OF 'FDETAILRECORD' (Cost=61711 C
ard=8789070 Bytes=123046980 )
select * from (
select id,to_char(date time,'yyyy-mm-dd hh24:mi:ss'),ro w_number() over
(order by id) rn from fdetailrecord
) where rn < 5;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOS E (Cost=82560 Card=8789070 B
ytes=325195590)

1 0 VIEW (Cost=82560 Card=8789070 Bytes=325195590 )
2 1 WINDOW (SORT PUSHED RANK) (Cost=82560 Card=8789070 Bytes
=123046980)

3 2 TABLE ACCESS (FULL) OF 'FDETAILRECORD' (Cost=61711 Car
d=8789070 Bytes=123046980 )
One advantage of the solution with analytic functions, is that you can
do:
select * from (
select id,to_char(date time,'yyyy-mm-dd hh24:mi:ss'),ro w_number() over
(order by id) rn from fdetailrecord
) where rn between 5 and 10;

Cu

Martin

[Holger Baer]

Martin Dachselt wrote:
[..]

Strange: costs and execution time seems bo be equal, even for big
tables. Not in my tests. The difference was small, but analytics always came last
(same table as in my previous post, but this time with 10 million rows).
The runtime was about 6.5 s for the rownum solution and 7s for the analytics
solution.
I thought the optimizer is guessing disk and cpu usage.
But they won't make any difference. A full scan is a fullscan, no matter
what you try. But note the difference in the cardinality and bytes between
the your plans. With rownum, the plan is expected to return exactly the
number of rows you asked for. With analytics, oracle cannot now how much
data you're going to retrieve, so it's pessimistic and expects the worst.

One advantage of the solution with analytic functions, is that you can
do:
select * from (
select id,to_char(date time,'yyyy-mm-dd hh24:mi:ss'),ro w_number() over
(order by id) rn from fdetailrecord
) where rn between 5 and 10;

But not really what the OP asked for, is it? And this one is equivalent
in every respect (except that it's slightly faster on my machine):

select * from (
select i, rownum rn from (select i,j from test order by j)
where rownum<= 10)
where rn between 5 and 10
/

To return to the original question:
select i from test where i between 10 and 15
and j in (select min(j) from test where i between 10 and 15)

is a working example of what the OP regarded as not possible (at least in 10g it works).
And with proper indexes in place this is pretty fast, too.

Perhaps we can get the OP to explain his problem better?

Regards,
Holger