Re: more sql help - find minimum

Select runid, min(event_time) over (partition by runid), ...   from ...
where ...
order by ...

Assumes EE, I believe, regardless of Oracle version.

There's no need to use the inline view and row_number() over if you're only interested in the least value.

On Thu, Jul 30, 2009 at 10:20 AM, Barbara Baker<barb.baker_at_gmail.com> wrote:
> 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
>
>
>

-- 
Adam Musch
ahmusch_at_gmail.com
--
http://www.freelists.org/webpage/oracle-l