Re: SQL Question

Your suggestion return the following result

    SCORE DIFFERENCE

--------- ----------
       12         -8
       14         -6
       19         -1
       20          0

What I want is

    SCORE DIFFERENCE
--------- ----------

       12         0
       14         2
       19         5
       20         1

=?EUC-KR?B?yLK787G4?= (angra_at_ns.maninet.co.kr) wrote:
: You mean in-line view in Oracle. How about this ?
:
: select a.score, a.score - b.maxval as difference
: from abc a, (select max(score) as maxval from abc) b
: order by a.score
:
: Ng K C Paul wrote:
:
: > 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