Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: subtracting values of sequential rows

Re: subtracting values of sequential rows

From: <sybrandb_at_hccnet.nl>
Date: Sun, 19 Aug 2007 11:27:58 +0200
Message-ID: <nj2gc3tfq0mhku52llqaeq8odttur7e8jr@4ax.com>


On Sun, 19 Aug 2007 07:34:45 GMT, mh_at_pixar.com wrote:

>Once a minute I collect a particular statistic, the total count of
>jobs completed. I can report on the last 5 minutes' progress
>with the query below.
>
>I would like to add on the the report the count of the jobs
>completed in that minute, which is of course the current minute's
>value of NCOMPLETED minus the previous minute's value of NCOMPLETED.
>
>Can I do this with a single SQL query? i.e., can I reference
>the value of NCOMPLETED for "rownum - 1"?
>
>select * from (
> select snaptime,ncompleted from op_thumbcounts
> order by snaptime desc
>) where rownum <= 5
>
>SNAPTIME NCOMPLETED (What I would like)
>------------------------- ----------------------
>19-AUG-07 00:17:00 2841878 140
>19-AUG-07 00:16:00 2841738 169
>19-AUG-07 00:15:00 2841569 159
>19-AUG-07 00:14:00 2841410
>19-AUG-07 00:13:00 2841220
>
>Many TIA!
>Mark

You need analytical functions, in particular the LAG function. The LAG function is documented in the datawarehousing manual. Other than that you can wait for Michel Cadot to do your work for you.

-- 
Sybrand Bakker
Senior Oracle DBA
Received on Sun Aug 19 2007 - 04:27:58 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US