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