Re: SQL Question

From: Jurij Modic <jmodic_at_src.si>
Date: 1998/09/17
Message-ID: <3600cedc.9831704@news.siol.net>#1/1

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 Thu Sep 17 1998 - 00:00:00 CDT