Re: select a where min(b)

From: steph <stephan0h_at_yahoo.de>
Date: Tue, 30 Mar 2010 06:10:18 -0700 (PDT)
Message-ID: <911ea776-a132-4a42-8517-c29d1438a6cc_at_x3g2000yqd.googlegroups.com>

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 Received on Tue Mar 30 2010 - 08:10:18 CDT