# Re: select a where min(b)

From: steph <stephan0h_at_yahoo.de>
Date: Thu, 1 Apr 2010 00:32:28 -0700 (PDT)

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  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  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

Original text of this message