Re: Set function SQL Help

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 01 Feb 2000 16:19:37 -0500
Message-ID: <i9je9sgm0s63v3v1kd665phmr8okeuj2m9@4ax.com>

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 )

 where rownum = 1
/

(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