Re: select a where min(b)

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Wed, 31 Mar 2010 22:44:09 +0200
Message-ID: <4bb3b425$0$22937$e4fe514c_at_news.xs4all.nl>

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 Received on Wed Mar 31 2010 - 15:44:09 CDT