Re: more sql help - find minimum

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-l