Re: select a where min(b)
Date: Tue, 30 Mar 2010 07:45:45 -0700 (PDT)
Message-ID: <3247ae79-d500-4238-b8cc-c53d23186114_at_h27g2000yqm.googlegroups.com>
On 30 Mrz., 16:20, ddf <orat..._at_msn.com> wrote:
> On Mar 30, 6:40 am, steph <stepha..._at_yahoo.de> wrote:
>
>
>
> > I've got a table STEP defined as (a number, b number). It contains
> > these data:
>
> > A,B
> > 1,1
> > 1,2
> > 1,3
> > 4,3
> > 2,3
> > 2,1
> > 5,0
>
> > Now I want to find this value of A where B is at it's minimum.
>
> > I made up the following SQL:
>
> > select a
> > from step
> > where b=
> > (
> > select min(b)
> > from step
> > )
>
> > But I suspect there must be a much more elegant way to achieve this.
> > Is there?
>
> > thanks,
> > Stephan
>
> SQL> create table step(a number, b number);
>
> Table created.
>
> SQL>
> SQL> begin
> 2 for i in 1..10 loop
> 3 insert into step values (i, mod(i,4));
> 4 end loop;
> 5 end;
> 6 /
>
> PL/SQL procedure successfully completed.
>
> SQL>
> SQL> select *
> 2 from step;
>
> A B
> ---------- ----------
> 1 1
> 2 2
> 3 3
> 4 0
> 5 1
> 6 2
> 7 3
> 8 0
> 9 1
> 10 2
>
> 10 rows selected.
>
> SQL>
> SQL> select a, b
> 2 from
> 3 (select a, b, dense_rank() over (order by b) rnk from step)
> 4 where rnk = 1;
>
> A B
> ---------- ----------
> 4 0
> 8 0
>
> SQL>
>
> David Fitzjarrell
David,
of course your statement is correct.
But I still think, the solution of the OP is more efficient.
If there is an index on B (which, I'm sure we agree - should be there anyway) you get the following plans:
SQL> create index step_idx on step(b);
Index created.
SQL> set autotrace on
SQL> select a, b
2 from
3 (select a, b, dense_rank() over (order by b) rnk from step)
4 where rnk = 1;
A B
---------- ----------
5 0
Execution Plan
Plan hash value: 286943537
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 7 | 273 | 3 (34)| 00:00:01 | |* 1 | VIEW | | 7 | 273 | 3 (34)| 00:00:01 | |* 2 | WINDOW SORT PUSHED RANK| | 7 | 182 | 3 (34)| 00:00:01 | | 3 | TABLE ACCESS FULL | STEP | 7 | 182 | 2 (0)|00:00:01 |
Predicate Information (identified by operation id):
1 - filter("RNK"=1)
2 - filter(DENSE_RANK() OVER ( ORDER BY "B")<=1)
Note
- dynamic sampling used for this statement
Statistics
5 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
464 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select a
2 from step
3 where b=
4 (
5 select min(b)
6 from step
7 ) ;
A
5
Execution Plan
Plan hash value: 3436790788
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 26 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | STEP | 1 | 26 | 1 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | STEP_IDX | 1 | | 1 (0)| 00:00:01 | | 3 | SORT AGGREGATE | | 1 | 13 | | | | 4 | INDEX FULL SCAN (MIN/MAX)| STEP_IDX | 7 | 91 |1 (0)| 00:00:01 |
Predicate Information (identified by operation id):
2 - access("B"= (SELECT MIN("B") FROM "STEP" "STEP"))
Note
- dynamic sampling used for this statement
Statistics
38 recursive calls
0 db block gets
25 consistent gets
0 physical reads
0 redo size
411 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
regards
Werner
Received on Tue Mar 30 2010 - 09:45:45 CDT