RE: Calcs between two rows of same table. (NEVERMIND)

From: Ron Thomas <rthomas_at_hypercom.com>
Date: Mon, 18 Jun 2001 14:00:45 -0700
Message-ID: <F001.0032DDE3.20010618140700@fatcity.com>

If you are on 8.1.6 or above, check out the new LEAD sql function.

Ron.

                                                                                       
                        
                    Rich.Jesse_at_qt                                                      
                        
                    iworld.com           To:     ORACLE-L_at_fatcity.com                  
                        
                    Sent by:             cc:                                           
                        
                    root_at_fatcity.        Subject:     RE: Calcs between two rows of 
same table. (NEVERMIND)    
                    com                                                                
                        
                                                                                       
                        
                                                                                       
                        
                    06/18/01                                                           
                        
                    01:45 PM                                                           
                        
                    Please                                                             
                        
                    respond to                                                         
                        
                    ORACLE-L

DOH! Answered my own question...

           select max(MYCOUNT)-min(MYCOUNT)
           from MYTABLE
           group by round(rownum/2+.5)*2
           UNION
           select max(MYCOUNT)-min(MYCOUNT)
           from MYTABLE
           group by round((rownum-1)/2+.5)*2

If I had just experimented 1 more minute before writing that message.... <blush>

Rich Jesse                          System/Database Administrator
Rich.Jesse_at_qtiworld.com             Quad/Tech International, Sussex, WI USA

-----Original Message-----
Sent: Monday, June 18, 2001 14:34
To: 'ORACLE-L_at_fatcity.com'

Hi all,

I've got a table, MYTABLE:

           MYCOUNT        number
           MYDATE                   date, UNIQUE

e.g:
           MYCOUNT        MYDATE
           12509                    06/17/2001 12:01
           12934                    06/17/2001 12:06
           13452                    06/17/2001 12:11
           13455                    06/17/2001 12:16

I need to get the difference between MYCOUNT on two consecutive rows
(consecutive MYDATEs). MYCOUNT cannot be less than the MYCOUNT
chronologically before it.

Is there a simple SELECT that I can use for this WITHOUT a cursor? I've come close:

           select max(MYCOUNT)-min(MYCOUNT)
           from MYTABLE
           group by round(rownum/2+.5)*2

But this only gives me diffs from rows 1, 2-3, 4-5, 6-7, etc., while I need the diffs from rows 1-2, 2-3, 3-4, 4-5... AAAALLLMOST!

TIA,

Rich Jesse                          System/Database Administrator
Rich.Jesse_at_qtiworld.com             Quad/Tech International, Sussex, WI USA

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jesse, Rich
  INET: Rich.Jesse_at_qtiworld.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ron Thomas
  INET: rthomas_at_hypercom.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

Received on Mon Jun 18 2001 - 16:00:45 CDT