Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Set function SQL Help
A copy of this was sent to jim.clark_at_bigfoot.com
(if that email address didn't require changing)
On Tue, 01 Feb 2000 20:36:23 GMT, you wrote:
>I need to write an SQL statement that gets the minumum value of an
>average, plus return an Id that is associated with that minimum average.
>
>Something like:
>select min( avg(avg_round_trip_time) )
>from stats
>group by id
>
>But have id in the select:
>select id, min( avg(avg_round_trip_time) )
>from stats
>group by id
>
select id, avg( avg_round_trip_time )
from stats
group by id
having avg( avg_round_trip_time )
<= ALL ( select avg(avg_round_trip_time) from stats group by id ) /
is one way. another is:
select id, avg( avg_round_trip_time )
from stats
group by id
having avg( avg_round_trip_time ) = ( select min(avg(avg_round_trip_time))
from stats group by id )/
or, in Oracle8i, release 8.1, you could:
select *
from ( select id, avg(avg_round_trip_time )
from stats group by id order by 2 )
(that'll NOT work in 8.0 or before)
>Is there a way to accomplish this with SQL?
>
>Thanks,
>Jim
>
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.
--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Feb 01 2000 - 15:19:37 CST