Re: SQL Question

From: Ng K C Paul <paulkcng_at_news.netvigator.com>
Date: 1998/09/18
Message-ID: <6tsebo$vml$2@imsp009a.netvigator.com>#1/1

Is there any other solution for oracle 7.0 and 7.1?

Since the following solution works on SQLServer not on Oracle

select a.score, (select a.score - max(b.score)

                 from abc b where b.score < a.score)
		 as different

from abc a

Jurij Modic (jmodic_at_src.si) wrote:
: On 17 Sep 1998 08:18:37 GMT, paulkcng_at_news.netvigator.com (Ng K C
: Paul) wrote:
:
: >Can you explain a little bit on the purpose of GROUP BY in the second query?
: >
: >Thanks!
:
: In short - it is used to force an inline views to return a result set
: in ordered fashion (since you can't use ORDER BY in a view).
:
: Here is a little longer explanation:
:
: Suppose the scores are entered into db blocks unsorted.
:
: SQL> SELECT * FROM abc;
:
: SCORE
: ---------
: 200
: 100
: 396
: 150
: 390
: 200
: 400
:
: 7 rows selected.
:
: My first query will display score and a difference to previously
: selected score in unsorted manner:
:
: SQL> SELECT x.score, NVL(x.score-y.score, 0) diff FROM
: 2 (SELECT score, rownum row_num FROM abc) x,
: 3 (SELECT score, rownum row_num FROM abc) y
: 4 WHERE x.row_num = y.row_num(+)+1;
:
: SCORE DIFF
: --------- ---------
: 200 0
: 100 -100
: 396 296
: 150 -246
: 390 240
: 200 -190
: 400 200
:
: 7 rows selected.
:
: The seccond query will return the same thing, but on ordered scores:
:
: SQL> SELECT x.score, NVL(x.score-y.score, 0) diff FROM
: 2 (SELECT x1.score, rownum row_num FROM
: 3 (SELECT score, rowid row_id FROM abc GROUP BY score, rowid
: 4 ) x1
: 5 )x,
: 6 (SELECT y1.score, rownum row_num FROM
: 7 (SELECT score, rowid row_id FROM abc GROUP BY score, rowid
: 8 ) y1
: 9 )y
: 10 WHERE x.row_num = y.row_num(+)+1;
:
: SCORE DIFF
: --------- ---------
: 100 0
: 150 50
: 200 50
: 200 0
: 390 190
: 396 6
: 400 4
:
: 7 rows selected.
:
: Note also that it propperly handles nonunique scores (score=200).
:
: As I said before, the GROUP BY is used to return the *ordered* result
: set. To properly handle the nonunique scores, I added ROWID (which is
: allways unique) in the grouping expression (othervise score=200 would
: be returned only once).
:
: With some modifications you can also force the ORDER BY to return
: result set in DESCENDING order if you want the scores and the
: differences to be returned in the descending order.
:
: 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 Fri Sep 18 1998 - 00:00:00 CDT