Re: more sql help - find minimum
From: Subodh Deshpande <subodh_deshpande_at_yahoo.com>
Date: Thu, 30 Jul 2009 08:38:49 -0700 (PDT)
Message-ID: <869044.86128.qm_at_web31101.mail.mud.yahoo.com>
Date: Thu, 30 Jul 2009 08:38:49 -0700 (PDT)
Message-ID: <869044.86128.qm_at_web31101.mail.mud.yahoo.com>
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_at_gmail.com> To: oracle-l_at_freelists.org 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
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Jul 30 2009 - 10:38:49 CDT