select a where min(b)
in [Oracle]
|
Prev: 2nd Listener
Next: #$%! Metalink is down again!!!
From: steph on 30 Mar 2010 06:40 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
From: Shakespeare on 30 Mar 2010 07:22 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
From: Carlos on 30 Mar 2010 08:55 On Mar 30, 1:22 pm, Shakespeare <what...(a)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(a)XE.localhost> create table t(a number(1) not null, b number(1) not null); Tabla creada. CARLOS(a)XE.localhost> insert into t values(1,1 ); 1 fila creada. CARLOS(a)XE.localhost> insert into t values(1,2 ); 1 fila creada. CARLOS(a)XE.localhost> insert into t values(1,3 ); 1 fila creada. CARLOS(a)XE.localhost> insert into t values(4,3 ); 1 fila creada. CARLOS(a)XE.localhost> insert into t values(2,3 ); 1 fila creada. CARLOS(a)XE.localhost> insert into t values(2,1 ); 1 fila creada. CARLOS(a)XE.localhost> insert into t values(5,0 ); 1 fila creada. CARLOS(a)XE.localhost> commit; Confirmación terminada. CARLOS(a)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(a)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(a)XE.localhost> HTH Cheers. Carlos.
From: Shakespeare on 30 Mar 2010 09:09 Op 30-3-2010 14:55, Carlos schreef: > I want to find this value of A where B is at it's minimum. To my opinion, this should return 5,0 Try the original query on your table! Shakespeare
From: steph on 30 Mar 2010 09:10
On 30 Mrz., 14:55, Carlos <miotromailcar...(a)netscape.net> wrote: > On Mar 30, 1:22 pm, Shakespeare <what...(a)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...(a)XE.localhost> create table t(a number(1) not null, b number(1) > not null); > > Tabla creada. > > CAR...(a)XE.localhost> insert into t values(1,1 ); > > 1 fila creada. > > CAR...(a)XE.localhost> insert into t values(1,2 ); > > 1 fila creada. > > CAR...(a)XE.localhost> insert into t values(1,3 ); > > 1 fila creada. > > CAR...(a)XE.localhost> insert into t values(4,3 ); > > 1 fila creada. > > CAR...(a)XE.localhost> insert into t values(2,3 ); > > 1 fila creada. > > CAR...(a)XE.localhost> insert into t values(2,1 ); > > 1 fila creada. > > CAR...(a)XE.localhost> insert into t values(5,0 ); > > 1 fila creada. > > CAR...(a)XE.localhost> commit; > > Confirmación terminada. > > CAR...(a)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...(a)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...(a)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 |