Re: SQL Question

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