Re: more sql help - find minimum
- From: Barbara Baker <barb.baker@xxxxxxxxx>
- To: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>
- Date: Thu, 30 Jul 2009 10:35:06 -0600
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:
