Re: sql comparison between records

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
+---