Re: select a where min(b)
From: steph <stephan0h_at_yahoo.de>
Date: Thu, 1 Apr 2010 00:32:28 -0700 (PDT)
Message-ID: <69f22659-fde1-420b-9192-50960b0f9e8a_at_l25g2000yqd.googlegroups.com>
On 31 Mrz., 22:44, Shakespeare <what..._at_xs4all.nl> wrote:
> Op 30-3-2010 22:55, ddf schreef:
>
>
>
> > 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
>
> Agree. I'd go for the original query, but I like the dense_rank example
> as a way of showing its use.
>
> Shakespeare
Date: Thu, 1 Apr 2010 00:32:28 -0700 (PDT)
Message-ID: <69f22659-fde1-420b-9192-50960b0f9e8a_at_l25g2000yqd.googlegroups.com>
On 31 Mrz., 22:44, Shakespeare <what..._at_xs4all.nl> wrote:
> Op 30-3-2010 22:55, ddf schreef:
>
>
>
> > 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
>
> Agree. I'd go for the original query, but I like the dense_rank example
> as a way of showing its use.
>
> Shakespeare
That's a real surprise for me that my original query is recommended.
Thought that some fancy analytic function would do better. Anyway
thanks everybody for their partivipation:)
regards,
stephan
Received on Thu Apr 01 2010 - 02:32:28 CDT