Re: select a where min(b)
From: ddf <oratune_at_msn.com>
Date: Tue, 30 Mar 2010 13:55:07 -0700 (PDT)
Message-ID: <e0165122-ef0d-4e02-888e-5e69e79371e7_at_r27g2000yqn.googlegroups.com>
On Mar 30, 10:45�am, magicwand <magicw..._at_gmx.at> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -
Date: Tue, 30 Mar 2010 13:55:07 -0700 (PDT)
Message-ID: <e0165122-ef0d-4e02-888e-5e69e79371e7_at_r27g2000yqn.googlegroups.com>
On Mar 30, 10:45�am, magicwand <magicw..._at_gmx.at> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -
Take it for what it is: a variant of Shakespeare's offering that will return all 'interested' rows. No claim was made of efficiency, and I see no issue with the original query. The OP, however, asked if there exists a more 'elegant' solution. I don't know how 'elegant' my offering may be, but it's presented for the sake of having another option to return the requested data.
David Fitzjarrell Received on Tue Mar 30 2010 - 15:55:07 CDT