Re: select a where min(b)

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Tue, 30 Mar 2010 15:11:33 +0200
Message-ID: <4bb1f88d$0$22935$e4fe514c_at_news.xs4all.nl>

Op 30-3-2010 15:10, steph schreef:

> On 30 Mrz., 14:55, Carlos<miotromailcar..._at_netscape.net>  wrote:

>> On Mar 30, 1:22 pm, Shakespeare<what..._at_xs4all.nl> wrote:
>>
>>
>>
>>> Op 30-3-2010 12:40, steph schreef:
>>
>>>> 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
>>
>>> If you want only one row returned :
>>
>>> select a,b from
>>> (select a,b,row_number() over (order by b,a) rown from step)
>>> where rown =1
>>
>>> Shakespeare
>>
>> I'm not sure Shakespeare's solution is what the OP is after:
>>
>> CAR..._at_XE.localhost> create table t(a number(1) not null, b number(1)
>> not null);
>>
>> Tabla creada.
>>
>> CAR..._at_XE.localhost> insert into t values(1,1 );
>>
>> 1 fila creada.
>>
>> CAR..._at_XE.localhost> insert into t values(1,2 );
>>
>> 1 fila creada.
>>
>> CAR..._at_XE.localhost> insert into t values(1,3 );
>>
>> 1 fila creada.
>>
>> CAR..._at_XE.localhost> insert into t values(4,3 );
>>
>> 1 fila creada.
>>
>> CAR..._at_XE.localhost> insert into t values(2,3 );
>>
>> 1 fila creada.
>>
>> CAR..._at_XE.localhost> insert into t values(2,1 );
>>
>> 1 fila creada.
>>
>> CAR..._at_XE.localhost> insert into t values(5,0 );
>>
>> 1 fila creada.
>>
>> CAR..._at_XE.localhost> commit;
>>
>> Confirmación terminada.
>>
>> CAR..._at_XE.localhost> select a,b from
>> 2 (select a,b,row_number() over (order by b,a) rown from t)
>> 3 where rown =1 ;
>>
>> A B
>> ---------- ----------
>> 5 0
>>
>> It makes more sense to me something like this:
>>
>> CAR..._at_XE.localhost> select a,b from
>> 2 (select a,b,row_number() over (partition by a order by b) rown
>> from t)
>> 3 where rown =1 ;
>>
>> A B
>> ---------- ----------
>> 1 1
>> 2 1
>> 4 3
>> 5 0
>>
>> CAR..._at_XE.localhost>
>>
>> HTH
>>
>> Cheers.
>>
>> Carlos.

>
> Hi,
>
> No, Shakespeares solution came quite near:
> I want the value(s) of A that appear where B is at it's minimum/
> maximum.
> Thanks for helping, though!
> regards,
> stephan

If you want more than one row, my solution is not correct.

Shakespeare Received on Tue Mar 30 2010 - 08:11:33 CDT