Re: select a where min(b)

From: Carlos <miotromailcarlos_at_netscape.net>
Date: Tue, 30 Mar 2010 05:55:15 -0700 (PDT)
Message-ID: <f1de06ea-90d2-442a-a1cb-3b817a0adc83_at_l25g2000yqd.googlegroups.com>

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:

CARLOS_at_XE.localhost> create table t(a number(1) not null, b number(1) not null);

Tabla creada.

CARLOS_at_XE.localhost> insert into t values(1,1 );

1 fila creada.

CARLOS_at_XE.localhost> insert into t values(1,2 );

1 fila creada.

CARLOS_at_XE.localhost> insert into t values(1,3 );

1 fila creada.

CARLOS_at_XE.localhost> insert into t values(4,3 );

1 fila creada.

CARLOS_at_XE.localhost> insert into t values(2,3 );

1 fila creada.

CARLOS_at_XE.localhost> insert into t values(2,1 );

1 fila creada.

CARLOS_at_XE.localhost> insert into t values(5,0 );

1 fila creada.

CARLOS_at_XE.localhost> commit;

Confirmación terminada.

CARLOS_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:

CARLOS_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

CARLOS_at_XE.localhost>

HTH Cheers.

Carlos. Received on Tue Mar 30 2010 - 07:55:15 CDT