Re: select a where min(b)

From: ddf <oratune_at_msn.com>
Date: Tue, 30 Mar 2010 07:20:00 -0700 (PDT)
Message-ID: <067b446e-9630-4556-a16d-101426b1b8f9_at_l36g2000yqb.googlegroups.com>

On Mar 30, 6:40 am, steph <stepha..._at_yahoo.de> wrote:
> 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

SQL> create table step(a number, b number);

Table created.

SQL>
SQL> begin

  2          for i in 1..10 loop
  3                  insert into step values (i, mod(i,4));
  4          end loop;

  5 end;
  6 /

PL/SQL procedure successfully completed.

SQL>
SQL> select *
  2 from step;

         A B
---------- ----------

         1          1
         2          2
         3          3
         4          0
         5          1
         6          2
         7          3
         8          0
         9          1
        10          2

10 rows selected.

SQL>
SQL> select a, b
  2 from
  3 (select a, b, dense_rank() over (order by b) rnk from step)   4 where rnk = 1;

         A B
---------- ----------

         4          0
         8          0

SQL> David Fitzjarrell Received on Tue Mar 30 2010 - 09:20:00 CDT