Re: select a where min(b)

From: steph <stephan0h_at_yahoo.de>
Date: Tue, 30 Mar 2010 06:17:57 -0700 (PDT)
Message-ID: <297c46b3-f11d-4243-9bd5-ccf78293e8f2_at_j21g2000yqh.googlegroups.com>

On 30 Mrz., 15:11, Shakespeare <what..._at_xs4all.nl> wrote:
> 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

I wouldn'd like to constrain it on the first row. Maybe the minimum/ maximum value of B appears in more than one row. (Sorry for my lousy testset).

regards,
stephan Received on Tue Mar 30 2010 - 08:17:57 CDT