Re: SQL Question

Can you explain a little bit on the purpose of GROUP BY in the second query?

Thanks!

Jurij Modic (jmodic_at_src.si) wrote:
: 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
Received on Thu Sep 17 1998 - 00:00:00 CDT