Re: Oracle Guru's: How to return additional row information

If you are using 8.1.6, you can use the
'analytic function' approach:

select

    server_name, sample_time, cpu_load
from (

    select server_name, sample_time, cpu_load,     rank() over (partition by server_name order by cpu_load desc) as ranked     from table_a
)
where ranked = 1
;

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

mikea730_at_my-deja.com wrote in message <8i704l$3bg$1_at_nnrp1.deja.com>...


>Anyone know a trick to do the following?

>

>Running Oracle 8i on Solaris 2.7

>

>In one select statement I need to determine the MAX() value for a

>column, then for the row that was found, return other information.  For

>example:

>

>Table_A conatains:

>  server_name VARCHAR2(30)

>  cpu_load    NUMBER,

>  sample_time DATE,

>

>In one select statement I'd like to see the following returned (GROUPED

>BY server_name)

>

>server_name  MAX(cpu_load)  sample_time

>-----------  -------------  --------------------

>server_1     100            4/10/2000 10:23:10

>server_2     56             5/23/2000 12:45:23

>server_3     33             2/12/2000 14:23:32

>

>The select statement to acheive this would be something like

>

>SELECT

>  server_name,

>  MAX(cpu_load)

>FROM

>  Table_A

>GROUP BY

>  server_name

>

>But the question is, how do I get the sample_time for the rows returned?

>

>Thanks in advance!!!

>

>Mike

>

>

>Sent via Deja.com http://www.deja.com/

>Before you buy.