Re: more sql help - find minimum

Yeah!! Yeah!!!
The code from you and from John Clark got me where I needed to be.
It was the partition ... over thingie that did it.

Thanks you guys SO MUCH!!!!!

Barb

On Thu, Jul 30, 2009 at 9:47 AM, Bobak, Mark <Mark.Bobak@xxxxxxxxxxxx>wrote:

>    1  select runid,
>
  2         event_time,
>
>   3         event_text,
>
>   4         firstgoodcopy
>
>   5    from (select runid,
>
>   6                 event_time,
>
>   7                 event_text,
>
>   8                 firstgoodcopy,
>
>   9                 row_number() over (partition by runid order by
> event_time) runids_by_time
>
>  10            from testit)
>
>  11*   where runids_by_time = 1
>
>
>
> -Mark
>
>
>
>
>
> *From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
> oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Barbara Baker
> *Sent:* Thursday, July 30, 2009 11:20 AM
> *To:* oracle-l@xxxxxxxxxxxxx
> *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: