| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle Guru's: How to return additional row information
SELECT t.server_name, t.cpu_load, t.sample_time FROM Table_A t,
(SELECT server_name, MAX(cpu_load) max_cpu_load
FROM Table_A
GROUP BY server_name) t1
AND t.cpu_load = t1.max_cpu_load
This will give you a list of ALL the sample time related to a MAX
(cpu_load)-server.
If server XXX had a MAX(cpu_load)=100 3 times, you'll have 3 rows.
If you want 1 row, you must use something like
SELECT t.server_name, t.cpu_load, MAX(t.sample_time) FROM Table_A t,
(SELECT server_name, MAX(cpu_load) max_cpu_load
FROM Table_A
GROUP BY server_name) t1
AND t.cpu_load = t1.max_cpu_load
GROUP BY t.server_name, t.cpu_load
or MIN or ... what you want !
-- be happy Eugenio remove _nospam from reply address Opinions are mine and do not necessarily reflect those of my company ======================================================= Sybrand Bakker wrote in message <960963685.5718.2.pluto.d4ee154e_at_news.demon.nl>...Received on Wed Jun 14 2000 - 00:00:00 CDT
>select server_name, sample_time
>from
>(
>SELECT
> server_name,
> MAX(cpu_load)
>FROM
> Table_A
>GROUP BY
> server_name
>) t1
>, (select sample_time
> from table_a
>) t2
>where t2.server_name = t1.server_name
>/
>
>Hth,
>
>Sybrand Bakker, Oracle DBA
>
>
>
><mikea730_at_my-deja.com> wrote in message news: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.
>
>
 |
 |