Re: more sql help - find minimum
- From: Subodh Deshpande <subodh_deshpande@xxxxxxxxx>
- To: barb.baker@xxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Thu, 30 Jul 2009 08:38:49 -0700 (PDT)
hi,
have you checked the indexes also..
in select you are selecing runid, eventtext, eventtime and in where as in
group by
group by pe.runid, pe.eventtime, pe.eventtext, ri.rt_runningstart, pi_issuedate
what if if you just interchange the column in select eventtime, eventtext
instead of eventtext,eventtime
13094 17-JUL-2009 00:48:22 Start net counter : 23270, 19515 16-JUL-2009
22:46:59 -- this is lowest
13095 17-JUL-2009 00:24:21 Start net counter : 27282, 24321 16-JUL-2009
22:31:42 -- this is lower
13095 17-JUL-2009 00:56:25 Start net counter : 52214, 48918 16-JUL-2009
22:31:42
13095 17-JUL-2009 01:47:40 Start net counter : 97484, 93424 16-JUL-2009
22:31:42
13095 17-JUL-2009 02:10:49 Start net counter : 100932, 97309 16-JUL-2009
22:31:42
13095 17-JUL-2009 02:22:03 Start net counter : 102168, 97329 16-JUL-2009
22:31:42
thanks...subodh
________________________________
From: Barbara Baker <barb.baker@xxxxxxxxx>
To: oracle-l@xxxxxxxxxxxxx
Sent: Thursday, 30 July, 2009 20:50:10
Subject: more sql help - find minimum
You guys were so generous with my last question that I'm going to ask another.
( I really am sorry, but they have pretty much laid off anyone here who can
even spell sql, and I've gone brain-dead)
Here is an example of my records:
Event
RUNID Time EventText FIRSTGOODCOPY
---------- -------------------- ----------------------------------
--------------------
13091 17-JUL-2009 00:22:52 Start net counter : 31556, 28351 16-JUL-2009
22:29:13
13091 17-JUL-2009 01:02:30 Start net counter : 66378, 62838 16-JUL-2009
22:29:13
13094 17-JUL-2009 00:25:39 Start net counter : 11180, 7659 16-JUL-2009
22:46:59
13094 17-JUL-2009 00:48:22 Start net counter : 23270, 19515 16-JUL-2009
22:46:59
13095 17-JUL-2009 00:24:21 Start net counter : 27282, 24321 16-JUL-2009
22:31:42
13095 17-JUL-2009 00:56:25 Start net counter : 52214, 48918 16-JUL-2009
22:31:42
13095 17-JUL-2009 01:47:40 Start net counter : 97484, 93424 16-JUL-2009
22:31:42
13095 17-JUL-2009 02:10:49 Start net counter : 100932, 97309 16-JUL-2009
22:31:42
13095 17-JUL-2009 02:22:03 Start net counter : 102168, 97329 16-JUL-2009
22:31:42
9 rows selected.
I need the earliest eventtime for a specific runid. (for runid 13095, I want
the single record returned for 00:24:21)
Here's my latest attempt, but it's not even close:
select
runid, eventtext, eventtime,
ri.rt_runningstart firstgoodcopy,
(select min(pev.eventtime) as min_time
from pecom_event pev, runinfo rif
where pev.runid = rif.run_runid
and pev.runid in (13091,13094,13095)
and eventtime >= to_char(trunc(ri.pi_issuedate-1),'DD-MON-YYYY')|| ' '
||
to_char(trunc(sysdate)
+ 23.75/24, 'HH24:MI:SS') ---eventtime ge 11:45pm on pubdate
)
from pecom_event pe, runinfo ri
where pe.runid = ri.run_runid
and pe.runid in (13091, 13094, 13095)
and eventtext like '%Start net%'
and eventtime >= rt_runningstart --- eventtime ge 1st good copy
and ri.run_productname like 'DPMAIN%'
and SUBSTR(eventtext,
INSTR(eventtext, ':', 1, 1)+1, --- position after leading colon
INSTR(eventtext, ',', 1,1)- --- position of leading comma
INSTR(eventtext, ':', 1, 1)-1) --- less pos of leading colon to get
string length
> 0
group by pe.runid, pe.eventtime, pe.eventtext, ri.rt_runningstart, pi_issuedate
/
thanks for any help!
Barb
Other related posts:
