Re: SQL Question

On 16 Sep 1998 08:34:32 GMT, paulkcng_at_news.netvigator.com (Ng K C Paul) wrote:

>I have a table abc like this
>
>score
>-----
>100
>150
>200
>300
>310
>350
>390
>395
>396
>400
>
>I want the result like this
>
>score difference
>----- ----------
>100 0
>150 50
>200 50
>300 100
>310 10
>350 40
>390 40
>395 5
>396 1
>400 4
>
>How can I select the result using SQL?

If the ordering of scores in the result set doesn't matter, then you can execute the following:

SELECT x.score, NVL(x.score-y.score, 0) diff FROM   (SELECT score, rownum row_num FROM abc) x,   (SELECT score, rownum row_num FROM abc) y WHERE x.row_num = y.row_num(+)+1;

The following query will select the scores in ascending order and also show the difference to the previously selected score.

SELECT x.score, NVL(x.score-y.score, 0) diff FROM   (SELECT x1.score, rownum row_num FROM
    (SELECT score, rowid row_id FROM abc GROUP BY score, rowid     ) x1
  )x,
  (SELECT y1.score, rownum row_num FROM
    (SELECT score, rowid row_id FROM abc GROUP BY score, rowid     ) y1
  )y
WHERE x.row_num = y.row_num(+)+1;

Both queries will work on Oracle 7.2 and above.

HTH,

--
Jurij Modic <jmodic_at_src.si>
Certified Oracle7 DBA (OCP)
================================================
The above opinions are mine and do not represent
any official standpoints of my employer