Re: select a where min(b)

From: Carlos <miotromailcarlos_at_netscape.net>
Date: Tue, 30 Mar 2010 07:49:08 -0700 (PDT)
Message-ID: <c3aefa2d-dbaf-43df-ade9-fd0339e53088_at_o30g2000yqb.googlegroups.com>

On Mar 30, 3:17 pm, steph <stepha..._at_yahoo.de> wrote:
> 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

I completely misunderstood the OP.

Then

SELECT A,B FROM STEP WHERE B IN ( SELECT MAX(B) FROM STEP) would suffice.

HTH. Cheers. Received on Tue Mar 30 2010 - 09:49:08 CDT