Re: How to do this trick ?

However,
read Thomas Kyte's post about Distinct and its sort behavior...It may not do what you expect...It should be 'findable' at deja news with sort and distinct as keywords and Thomas Kyte as the author...

rtproffitt_at_my-deja.com wrote:

>Hello,
>
>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
> (select Rownum FirstRow, MyNum FirstNum
> from (select distinct mynum from bob) FirstTbl) a,
> (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.

  -----------== Posted via Newsfeeds.Com, Uncensored Usenet News ==----------    http://www.newsfeeds.com The Largest Usenet Servers in the World! ------== Over 73,000 Newsgroups - Including Dedicated Binaries Servers ==----- Received on Fri Oct 15 1999 - 20:23:46 CEST