Home » SQL & PL/SQL » SQL & PL/SQL » SQL - Need Help Urgently
SQL - Need Help Urgently [message #305461] Mon, 10 March 2008 18:05 Go to next message
sumit_can
Messages: 5
Registered: June 2007
Location: Austin
Junior Member
I have a table with data row value as Daily Top 5 slowest running jobs.Some of the data row values is displayed below:
Table Name: DLY_TOP5_JOB

DATE1 APP_NM JOB_NM ELAPSED_TIME
--------- ------ ------ ------------
25-FEB-08 APP1 JOB09 34
25-FEB-08 APP1 JOB2B 26
25-FEB-08 APP1 JOBZ9 25
25-FEB-08 APP1 JOBZ8 19
25-FEB-08 APP1 JOB38 13
26-FEB-08 APP2 JOB09 25
26-FEB-08 APP2 JOB54 22
26-FEB-08 APP2 JOB18 19
26-FEB-08 APP2 JOB05 14
26-FEB-08 APP2 JOB04 11

Table Structure is shown below:
CREATE TABLE DLY_TOP5_JOB
(
DATE1 DATE,
APPLICATION_NAME VARCHAR2(5 BYTE) NOT NULL,
JOB_NAME VARCHAR2(30 BYTE) NOT NULL,
ELAPSED_TIME NUMBER(5) NOT NULL
)

My Query to you:
----------------
I am trying to retrieve values of the Job Name,it's maximum Elapsed Time & the Date (on which the maximum elapsed time occured), and number of times the Job was on the Top 5 lists (for the month of February'2008).

I have written below query , but I not sure how to select the value of "Date" on which the maximum Elapsed Time occured (for each Jobs):


select a.job_name "Job Name",
max(a.elapsed_time) "Max Elapsed Time",
(select count(*) from DLY_TOP5_JOB c where a.job_name=c.job_name) COUNT
from DLY_TOP5_JOB a,DLY_TOP5_JOB m
where a.elapsed_time=(select max(b.elapsed_time) from DLY_TOP5_JOB b
where b.job_name=m.job_name)
group by a.job_name
order by max(a.elapsed_time) desc

Your help will be appreciated.Please guide me.
Thanks
Re: SQL - Need Help Urgently [message #305463 is a reply to message #305461] Mon, 10 March 2008 18:40 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_11g> SELECT a.job_name	  "Job Name",
  2  	    a.max_elapsed_time "Max Elapsed Time",
  3  	    b.date1	       "The Date",
  4  	    a.count
  5  FROM   (SELECT job_name,
  6  		    MAX (elapsed_time) max_elapsed_time,
  7  		    COUNT (*) count
  8  	     FROM   dly_top5_job
  9  	     GROUP  BY job_name) a,
 10  	    dly_top5_job b
 11  WHERE  a.job_name = b.job_name
 12  AND    a.max_elapsed_time = b.elapsed_time
 13  ORDER  BY max_elapsed_time DESC
 14  /

Job Name                       Max Elapsed Time The Date         COUNT
------------------------------ ---------------- ----------- ----------
JOB09                                        34 25-FEB-2008          2
JOB2B                                        26 25-FEB-2008          1
JOBZ9                                        25 25-FEB-2008          1
JOB54                                        22 26-FEB-2008          1
JOBZ8                                        19 25-FEB-2008          1
JOB18                                        19 26-FEB-2008          1
JOB05                                        14 26-FEB-2008          1
JOB38                                        13 25-FEB-2008          1
JOB04                                        11 26-FEB-2008          1

9 rows selected.

SCOTT@orcl_11g> 

Re: SQL - Need Help Urgently [message #305494 is a reply to message #305461] Tue, 11 March 2008 00:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Next time, please read OraFAQ Forum Guide, especially "How to format your post?" section and use of title and words like "urgent".
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: SQL - Need Help Urgently [message #305709 is a reply to message #305463] Tue, 11 March 2008 11:38 Go to previous messageGo to next message
sumit_can
Messages: 5
Registered: June 2007
Location: Austin
Junior Member
Thankyou, Barbara.

But with your given SQL , it also fetches the duplicates Job names, as shown below:

Job Name Max Elapsed Time The Date COUNT
-------- ------------- ----------- ----------
JOB09 34 25-FEB-2008 2
JOB2B 26 25-FEB-2008 1
JOBZ9 25 25-FEB-2008 1
JOB54 22 26-FEB-2008 1
JOBZ8 19 25-FEB-2008 1
JOB18 19 26-FEB-2008 1
JOB05 14 26-FEB-2008 1
JOB38 13 25-FEB-2008 1
JOB04 11 26-FEB-2008 1
JOB02 6 28-FEB-2008 9
JOB02 6 03-MAR-2008 9
JOB02 6 06-MAR-2008 9
JOB02 6 07-MAR-2008 9
JOB03 8 25-FEB-2008 9
JOB03 8 03-MAR-2008 9
JOB03 8 06-MAR-2008 9

Is there a possibility to get distinct values for Job Names?

With my below query , I am able to get distinct rows for Job names, but it does not works for the "Date" on which the slowest Time occured:

select a.job_name "Job Name",
max(a.elapsed_time) "Max Elapsed Time",
(select count(*) from DLY_TOP5_JOB c where a.job_name=c.job_name) COUNT
from DLY_TOP5_JOB a,DLY_TOP5_JOB m
where a.elapsed_time=(select max(b.elapsed_time) from DLY_TOP5_JOB b
where b.job_name=m.job_name)
group by a.job_name
order by max(a.elapsed_time) desc

Thankyou , once again.
Re: SQL - Need Help Urgently [message #305714 is a reply to message #305709] Tue, 11 March 2008 12:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is there a possibility you follow the rules?

Regards
Michel
Re: SQL - Need Help Urgently [message #305717 is a reply to message #305714] Tue, 11 March 2008 12:47 Go to previous messageGo to next message
sumit_can
Messages: 5
Registered: June 2007
Location: Austin
Junior Member
Sure, Michel.

Does the below format looks OK to you, now:
FYI - I will make sure, next time I will take care while using the word "urgent" or "urgently".Thanks.

Job Name Max Elapsed Time The Date COUNT
APPXIB09 67 01-MAR-08 11
APPXIB14 8 07-MAR-08 9
APPJOB15 5 25-FEB-08 9
APPJOB15 5 26-FEB-08 9
APPJOB15 5 27-FEB-08 9
APPJOB15 5 29-FEB-08 9
APPJOB15 5 01-MAR-08 9
APPJOB15 5 04-MAR-08 9
APPJOB15 5 06-MAR-08 9
APPJOB15 5 07-MAR-08 9
APPJOB15 5 10-MAR-08 9
APPXIB18 49 01-MAR-08 10
APPXICHR 17 29-FEB-08 4
APPXII02 10 29-FEB-08 2
APPXII03 24 29-FEB-08 2
APPXII04 76 29-FEB-08 3
APPXII58 1 03-MAR-08 1
APPXII59 1 03-MAR-08 1
APPXISAC 1 03-MAR-08 1
APPXPB02 7 01-MAR-08 2
APPXPB02 7 03-MAR-08 2
APPXPB03 7 03-MAR-08 1

Is there a possibility to get distinct values for Job Names?


[Updated on: Tue, 11 March 2008 13:09]

Report message to a moderator

Re: SQL - Need Help Urgently [message #305718 is a reply to message #305717] Tue, 11 March 2008 13:05 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
sumit_can wrote on Tue, 11 March 2008 13:47
Sure, Michel.

Does the below format looks OK to you, now:



Does it look ok you you? Can you see the difference between your and this?
Job Name 	Max Elapsed Time 	The Date 	COUNT
JOBZ9 	        25	                25-Feb-08	1
JOB54 	        22	                26-Feb-08	1
Re: SQL - Need Help Urgently [message #305737 is a reply to message #305717] Tue, 11 March 2008 15:01 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
When the same max_elapsed_time occurs on different dates, which one do you want? For example, in the portion of your sample results below, do you want March 1st or March 3rd and why?

APPXPB02 7 01-MAR-08 2
APPXPB02 7 03-MAR-08 2
Re: SQL - Need Help Urgently [message #305742 is a reply to message #305461] Tue, 11 March 2008 15:49 Go to previous messageGo to next message
sumit_can
Messages: 5
Registered: June 2007
Location: Austin
Junior Member
I wish to retrieve the first date on which the maximum elapsed time occured. In this example , I wish to get the date of 1st March'2008 , ie, the data row value -> "APPXPB02 7 01-MAR-08 2"

Since, the maximum elapsed_time occurred firstly on this date (ie, 1st March'2008), I can analyze further of what went wrong or why did it ran slow on the first date (& then , I can Tune-up the slow running job with rerence to Performance-Tuning , accordingly) .

Thanks, in advance.
Re: SQL - Need Help Urgently [message #305745 is a reply to message #305742] Tue, 11 March 2008 18:18 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
In that case, just apply MIN to the date and GROUP BY the other columns:

SELECT a.job_name         "Job Name", 
       a.max_elapsed_time "Max Elapsed Time", 
       MIN (b.date1)      "The Date", 
       a.count
FROM   (SELECT job_name, 
               MAX (elapsed_time) max_elapsed_time,
               COUNT (*) count
        FROM   dly_top5_job
        GROUP  BY job_name) a,
       dly_top5_job b
WHERE  a.job_name = b.job_name
AND    a.max_elapsed_time = b.elapsed_time
GROUP  BY a.job_name, a.max_elapsed_time, a.count
ORDER  BY max_elapsed_time DESC;

Re: SQL - Need Help Urgently [message #306054 is a reply to message #305461] Wed, 12 March 2008 12:37 Go to previous message
sumit_can
Messages: 5
Registered: June 2007
Location: Austin
Junior Member
Thankyou , Barbara.That works!
Previous Topic: concatenation of a string and an autoincremented sequence
Next Topic: Insert into when there is a Union in the stmt
Goto Forum:
  


Current Time: Wed Dec 07 04:57:51 CST 2016

Total time taken to generate the page: 0.06419 seconds