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