Re: sql comparison between records

Bruce,

Do you want someone to try this on V5R1?  Is "fetch first" available on DB2 
UDC Ver 7.1?

-- Charly

----------------------------
Sanders Sauls for President!

For their next act, they'll no doubt be buying a firewall running under NT, 
which makes about as much sense as building a prison out of meringue.  -- 
Tanuki

Charly Jones
253 851-9876
Gig Harbor Washington

---------------------
DISCLAIMER

Any opinions expressed in this email are those of the individual
and not necessarily the company.  This email and any files
transmitted with it, including replies and forwarded copies (which
may contain alterations) subsequently transmitted from the
Company, are confidential and solely for the use of the intended
recipient.  It may contain material protected by attorney-client
privilige.  If you are not the intended recipient or the person
responsible for delivering to the intended recipient, be advised
that you have received this email in error and that any use is
strictly prohibited.



>From: "R. Bruce Hoffman, Jr." <rbruceh@attglobal.net>
>Reply-To: MIDRANGE-L@midrange.com
>To: <MIDRANGE-L@midrange.com>
>Subject: Re: sql comparison between records
>Date: Fri, 20 Jul 2001 18:51:46 -0400
>
>hmmm, no go on UDB V 7.1, so probably no on 400...
>
>
>
>===========================================================
>R. Bruce Hoffman, Jr.
>  -- IBM Certified Specialist - AS/400 Administrator
>  -- IBM Certified Specialist - RPG IV Developer
>
>"America is the land that fought for freedom and then
>   began passing laws to get rid of it."
>
>      - Alfred E. Neuman
>
>-----Original Message-----
>From: R. Bruce Hoffman, Jr. <rbruceh@attglobal.net>
>To: MIDRANGE-L@midrange.com <MIDRANGE-L@midrange.com>
>Date: Friday, July 20, 2001 6:35 PM
>Subject: Re: sql comparison between records
>
>
> >-----Original Message-----
> >From: Joel Fritz <JFritz@sharperimage.com>
> >To: MIDRANGE-L@midrange. com (E-mail) <MIDRANGE-L@midrange.com>
> >Date: Friday, July 20, 2001 1:20 PM
> >Subject: sql comparison between records
> >
> >
> >>How do you do this in SQL?
> >>
> >>I have a file ordered by key value and a timestamp.  There are multiple
> >>records for each key value. I want to find the difference between time
> >>stamps of successive records and calculate the average difference for a
>key
> >>value group.  I know, it's easy in RPG.  I'm not trying to measure
> >>programmer productivity. <g>
> >>
> >>>Hey. Good question.
> >>>
> >>>At first blush:
> >>>
> >>>select a.key, avg(a.ts - (select min(b.ts) from theFile b where b.ts >
> >a.ts
> >>>and a.key = b.key)) from theFile a group by a.key
> >>>
> >>>but I can all but guarantee that that won't work.
> >>>
> >>>Maybe with a common table expression or a view....
> >>>
> >>>signed, pondering in pennsylvania....   ;-)
> >>
> >>
> >>ok, second blush... it's recusive. that means NO in 400 lingo. In UDB, 
>it
> >>still probably can't be done because you can't use a column function in
>the
> >>recursive subquery....
> >>
> >>hhhhmmmmm. Also need some kind of "seq" to locate next.....
> >>
> >>still pondering in pennsylvania...
> >>
> >
> >
> >And now I'm in search of a V5R1 machine for a couple of minutes.... Al?
> >
> >Anyway, the theory is this... with 5.1 there is a fetch first row only
> >clause... maybe a correlated subquery?
> >
> >
> >select a.key, avg((select b.ts from theFile b where b.ts > a.ts and b.key 
>=
> >a.key fetch first row only) - a.ts) from theFile a group by a.key
> >
> >
> >hhhhhhmmmmmmmmmmmmm..........  <seg>
> >
> >
> >+---
> >| This is the Midrange System Mailing List!
> >| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
> >| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
> >| To unsubscribe from this list send email to
>MIDRANGE-L-UNSUB@midrange.com.
> >| Questions should be directed to the list owner/operator:
>david@midrange.com
> >+---
>
>+---
>| This is the Midrange System Mailing List!
>| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
>| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
>| To unsubscribe from this list send email to 
>MIDRANGE-L-UNSUB@midrange.com.
>| Questions should be directed to the list owner/operator: 
>david@midrange.com
>+---


_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp

+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---