SQL Question
Ng K C Paul
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?
luc van der veurst
: I have a table abc like this
create table s (score int);
select min(score) score, 0 difference
from s
union
select s2.score, s2.score - s1.score difference
from s s1, s s2
where s1.score < s2.score
and s2.score = (select min(score)
from s s3
where s3.score > s1.score)
order by score
--
Luc.
______________________________________________________________________
Luc Van der Veurst ISUG President
Academic Hospital, VUB http://www.isug.com
Laarbeeklaan 101, 1090 Brussels, Belgium
32 - 2 477 69 80 Next ISUG Conference :
lu...@az.vub.ac.be Hamburg, Germany October, 5-9 1998
阿樂仕
try this :
select a.score, isnull((select (a.score - max(b.score))
from abc b where b.score < a.score),0) as different
from abc a
i've tested it in Sybase 11.02 and work fine !
HTH
p.s. are u HKese ? (we are using the same isp ;)
Aronx,
8^)
Jurij Modic
Paul) wrote:
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 <jmo...@src.si>
Certified Oracle7 DBA (OCP)
================================================
The above opinions are mine and do not represent
any official standpoints of my employer
Ng K C Paul
it out in Oracle too. Yes, I live in Hong Kong.
阿樂仕 (ow...@geocities.com) wrote:
: Hi Paul,
:
: try this :
: select a.score, isnull((select (a.score - max(b.score))
: from abc b where b.score < a.score),0) as different
: from abc a
:
: i've tested it in Sybase 11.02 and work fine !
:
: HTH
:
: p.s. are u HKese ? (we are using the same isp ;)
:
: Aronx,
: 8^)
:
:
:
:
Ng K C Paul
Thanks!
Jurij Modic (jmo...@src.si) wrote:
: On 16 Sep 1998 08:34:32 GMT, paul...@news.netvigator.com (Ng K C
: If the ordering of scores in the result set doesn't matter, then you
Jurij Modic
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.
Ng K C Paul
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 (jmo...@src.si) wrote:
: On 17 Sep 1998 08:18:37 GMT, paul...@news.netvigator.com (Ng K C
황상구
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:
Ng K C Paul
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?= (an...@ns.maninet.co.kr) wrote:
: You mean in-line view in Oracle. How about this ?
:
Jurij Modic
Paul) wrote:
>Is there any other solution for oracle 7.0 and 7.1?
In Oracle prior to 7.2 inline views are not supported. There you have
to create a view (aliased "x" and "y" in my original queries)
explicitely and then select directly from this view, e.g:
CREATE VIEW v1 AS SELECT score, rownum row_num FROM abc;
SELECT x.score, NVL(x.score-y.score, 0) diff FROM v1 x, v2 y
WHERE x.row_num = y.row_num(+)+1;
HTH,
Ng K C Paul
inline view?
Jurij Modic (jmo...@src.si) wrote:
: On 18 Sep 1998 01:51:52 GMT, paul...@news.netvigator.com (Ng K C
Ng K C Paul
databases.
However, it can't handle duplicate score.
Thanks again!
luc van der veurst (lu...@uranus.az.vub.ac.be.) wrote:
: In comp.databases.sybase Ng K C Paul <paul...@news.netvigator.com> 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?
:
: create table s (score int);
luc van der veurst
: databases.
: However, it can't handle duplicate score.
Well, your example didn't contain duplicate scores ... :-). and so doesn't
say what should happen if there are.
How about adding 'distinct' to the second query in the union ?
Ng K C Paul
the following testing table abc
score
-----------
1
1
2
3
6
9
select min(score) score, 0 difference
from abc
union
select s2.score, s2.score - s1.score difference
from abc s1, abc s2
where s1.score < s2.score
and s2.score = (select min(score)
from abc s3
where s3.score > s1.score)
order by score
score difference
----------- -----------
1 0
2 1
3 1
6 3
9 3
The first two lines are changed from 1,1 to 1,2
luc van der veurst (lu...@uranus.az.vub.ac.be.) wrote:
Jurij Modic
Paul) wrote:
>Is there any performance difference in using permanent view and temporary
>inline view?
No, I don't think so (ther might be some minimal differences during
the parsing phase, but I belive you can neglect them).
Regards,