Re: How to do this trick ?

Hello,

[Quoted] Just for the sake of practice, here is a completely different approach using inline tables.

Table Bob: MyNum Number
RT>select * from bob;

    MYNUM

        3
        1
        4
        7
       10
       11
       12

select rownum, FirstNum, PrevNum, Firstnum-PrevNum Diff From
[Quoted] (select Rownum FirstRow, MyNum FirstNum
   from (select distinct mynum from bob) FirstTbl) a,
[Quoted] (Select Rownum+1 PrevRow, MyNum PrevNum
   from (select distinct mynum from bob) PrevTbl) b Where FirstRow=PrevRow

   ROWNUM FIRSTNUM PREVNUM DIFF
--------- --------- --------- ---------

        1         3         1         2
        2         4         3         1
        3         7         4         3
        4        10         7         3
        5        11        10         1
        6        12        11         1

1. The distinct forces a sort on innermost tables. This organizes the data into ascending order (You might test using order by inside an inline table, but didn't work for me).
2. The next/outer inline table add Row numbers to the sorted data. Second inline table get Rownum + 1 because Rownum will later become the join key and we want N row to join up with previous row.
3. Once joined, the Difference calculated.

Robert Proffitt
Beckman Coulter

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Oct 15 1999 - 19:11:51 CEST