Re: more sql help - find minimum

From: Mathias Magnusson <mathias.magnusson_at_gmail.com>
Date: Thu, 30 Jul 2009 17:49:26 +0200
Message-ID: <8580d4110907300849kba9bd66oad90a2ddb65fbc0_at_mail.gmail.com>

Barb,
Something like this ought to do it.

select *
  from table_a a
 where a.event_time = (select min(event_time)

                         from table_a b
                        where a.runid = b.runid)

That is the basic construct, as for what you need in your query, I'd guess on something like this.

select
  runid, eventtext, eventtime,
  ri.rt_runningstart firstgoodcopy,
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
  and pe.event_time = (select min(event_time)

                         from pecom_event b
                        where pe.runid = b.runid)

I'm not sure enough on your tables to know if that is what you need, but it may be close enough that you can tweak which table you take what from to get it to work. If not, post again and someone will surely suggest a needed change or an alternative solution.

Mathias

On Thu, Jul 30, 2009 at 5:20 PM, 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
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jul 30 2009 - 10:49:26 CDT