Re: Limitting result without ROWNUM

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 20 Jan 2007 14:37:13 -0800
Message-ID: <1169332633.876149.180150@11g2000cwr.googlegroups.com>

DA Morgan wrote:
> Charles Hooper wrote:
> > Method #3:
> > SELECT
> > R.ID,
> > FIRSTNAME,
> > LASTNAME,
> > HEIGHT,
> > AGE
> > FROM
> > (SELECT
> > ID,
> > ROW_NUMBER() OVER (ORDER BY HEIGHT DESC) POSITION
> > FROM
> > T1) R,
> > T1
> > WHERE
> > R.POSITION<=10
> > AND R.ID=T1.ID;
> >
> > The results of the above methods look something like this:
> > ID FIRSTNAME LASTNAME HEIGHT AGE
> > ---------------------------------------
> > 2 SUSAN SMITH 65 20
> > 3 DOROTHY SMITH 62 21
> > 4 JOHN SMITH 72 35
> > 5 DAVID SMITH 73 34
> > 7 ROBERT SMITH 76 45
> > 10 SUSAN JOHNSON 65.5 20
> > 11 DOROTHY JOHNSON 62.5 21
> > 12 JOHN JOHNSON 72.5 35
> > 13 DAVID JOHNSON 73.5 34
> > 15 ROBERT JOHNSON 79 45
> >
> > Charles Hooper
> > PC Support Specialist
> > K&M Machine-Fabricating, Inc.

>

> Impressive but an impressive contrivance. But ROW_NUMBER, ROWNUM, the
> difference is? ;-)

>

> Why not just build a table with ten rows and then use it the way we
> use dual as a source of a single result?
> --
> Daniel A. Morgan
> University of Washington
> damorgan_at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Group
> www.psoug.org

Good question, what is the difference between ROW_NUMBER and ROWNUM, other than ROWNUM was specifically prohibited, while ROW_NUMBER was not? Let's try an experiment:
SELECT
  ID,
  ROW_NUMBER() OVER (ORDER BY HEIGHT DESC) POSITION,   ROWNUM
FROM
  T1
ORDER BY
  HEIGHT DESC; In the above, I used the analytical function ROW_NUMBER(), which sorted by height in descending order, and I requested the pseudo column ROWNUM and applied an ORDER BY HEIGHT DESC clause. So, ROW_NUMBER and ROWNUM should return the same values, right?

        ID POSITION ROWNUM
---------- ---------- ----------

        15          1         15
         7          2          7
        13          3         13
         5          4          5
        12          5         12
         4          6          4
        10          7         10
         2          8          2
        11          9         11
         3         10          3
         9         11          9
         1         12          1
         6         13          6
        14         14         14
        16         15         16
         8         16          8

The above, unexplained result used to cause me much confusion from time to time. Until I realized, after reading an article written by Tom Kyte in Oracle Magazine, that the ROWNUM is generated before the ORDER BY clause takes effect.

To obtain the correct values for ROWNUM, we need to slide the previous results into an inline view:
SELECT
  ID,
  POSITION,
  ROWNUM1,
  ROWNUM ROWNUM2
FROM
  (SELECT
    ID,
    ROW_NUMBER() OVER (ORDER BY HEIGHT DESC) POSITION,     ROWNUM ROWNUM1
  FROM
    T1
  ORDER BY
    HEIGHT DESC);         ID POSITION ROWNUM1 ROWNUM2 ---------- ---------- ---------- ----------

        15          1         15          1
         7          2          7          2
        13          3         13          3
         5          4          5          4
        12          5         12          5
         4          6          4          6
        10          7         10          7
         2          8          2          8
        11          9         11          9
         3         10          3         10
         9         11          9         11
         1         12          1         12
         6         13          6         13
        14         14         14         14
        16         15         16         15
         8         16          8         16

So possibily, the requirement not to use ROWNUM was a request at better optimization, by requiring one less inline view to obtain the answer. :-)

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Sat Jan 20 2007 - 16:37:13 CST