select a where min(b)
in [Oracle]
|
Prev: 2nd Listener
Next: #$%! Metalink is down again!!!
From: Shakespeare on 30 Mar 2010 09:11 Op 30-3-2010 15:10, steph schreef: > 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 If you want more than one row, my solution is not correct. Shakespeare
From: steph on 30 Mar 2010 09:17 On 30 Mrz., 15:11, Shakespeare <what...(a)xs4all.nl> wrote: > Op 30-3-2010 15:10, steph schreef: > > > > > 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 > > 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
From: ddf on 30 Mar 2010 10:20 On Mar 30, 6:40 am, steph <stepha...(a)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
From: magicwand on 30 Mar 2010 10:45 On 30 Mrz., 16:20, ddf <orat...(a)msn.com> wrote: > On Mar 30, 6:40 am, steph <stepha...(a)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 David, of course your statement is correct. But I still think, the solution of the OP is more efficient. If there is an index on B (which, I'm sure we agree - should be there anyway) you get the following plans: SQL> create index step_idx on step(b); Index created. SQL> set autotrace on 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 ---------- ---------- 5 0 Execution Plan ---------------------------------------------------------- Plan hash value: 286943537 --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 7 | 273 | 3 (34)| 00:00:01 | |* 1 | VIEW | | 7 | 273 | 3 (34)| 00:00:01 | |* 2 | WINDOW SORT PUSHED RANK| | 7 | 182 | 3 (34)| 00:00:01 | | 3 | TABLE ACCESS FULL | STEP | 7 | 182 | 2 (0)| 00:00:01 | --------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RNK"=1) 2 - filter(DENSE_RANK() OVER ( ORDER BY "B")<=1) Note ----- - dynamic sampling used for this statement Statistics ---------------------------------------------------------- 5 recursive calls 0 db block gets 15 consistent gets 0 physical reads 0 redo size 464 bytes sent via SQL*Net to client 416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1 rows processed SQL> select a 2 from step 3 where b= 4 ( 5 select min(b) 6 from step 7 ) ; A ---------- 5 Execution Plan ---------------------------------------------------------- Plan hash value: 3436790788 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 26 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | STEP | 1 | 26 | 1 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | STEP_IDX | 1 | | 1 (0)| 00:00:01 | | 3 | SORT AGGREGATE | | 1 | 13 | | | | 4 | INDEX FULL SCAN (MIN/MAX)| STEP_IDX | 7 | 91 | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("B"= (SELECT MIN("B") FROM "STEP" "STEP")) Note ----- - dynamic sampling used for this statement Statistics ---------------------------------------------------------- 38 recursive calls 0 db block gets 25 consistent gets 0 physical reads 0 redo size 411 bytes sent via SQL*Net to client 416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> regards Werner
From: Carlos on 30 Mar 2010 10:49
On Mar 30, 3:17 pm, steph <stepha...(a)yahoo.de> wrote: > On 30 Mrz., 15:11, Shakespeare <what...(a)xs4all.nl> wrote: > > > > > Op 30-3-2010 15:10, steph schreef: > > > > 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 > > > 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. |