Re: Limitting result without ROWNUM

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 20 Jan 2007 05:24:06 -0800
Message-ID: <1169299446.811159.308010@38g2000cwa.googlegroups.com>

Charles Hooper wrote:
> Matthias Matker wrote:
> > Vladimir M. Zakharychev schrieb:
> > >> I have to limit the number of results without using ROWNUM or something
> > >> like that. One hint was to use "JOIN".
> > >>
> > >> I have to select the 10 biggest persons from a table "persons"
> > >>
> > >> id, firstname, lastname, size, age
> > >>
> > >>
> > >> WITHOUT using ROWNUM or "limitter" like this.
> > >>
> > >> Do you have any idea?
> > >>
> > >> Thanks a lot.
> > >
> > > Strange requirement - care to explain why?
> >
> > It forbidden to use such "limiters", we ( students) should do this via a
> > kind of "JOIN". We MUST do so.
> > I have tried it for hours...
> >
> > > I'd suggest using RANK() or
> > > DENSE_RANK() analytic functions for this, but you might consider them
> > > "limiters", too, so...
> >
> > That's right ;-)
> >
> > > How about just fetching the first 10 rows of the
> > > result set and ignoring the rest?
> >
> > Cannot act like this.
> >
> > Thanks for your quick reply.

>

> OK, if I understand:
> * No ROWNUM may be used
> * Hint is that we should use a join

>

> Method #1:
> SELECT
> ID,
> FIRSTNAME,
> LASTNAME,
> HEIGHT,
> AGE
> FROM
> (SELECT
> LAG(ID,1,-1) OVER (ORDER BY HEIGHT DESC) SIGNAL,
> HEIGHT END_HEIGHT,
> LEAD(HEIGHT,9,0) OVER (ORDER BY HEIGHT DESC) START_HEIGHT
> FROM
> T1) R,
> T1
> WHERE
> R.SIGNAL=-1
> AND T1.HEIGHT BETWEEN R.START_HEIGHT AND R.END_HEIGHT;

>

> Method #2:
> SELECT
> T1.ID,
> FIRSTNAME,
> LASTNAME,
> HEIGHT,
> AGE
> FROM
> (SELECT
> ID,
> PERCENT_RANK() OVER (ORDER BY T1.HEIGHT DESC) POSITION,
> TR.TOTAL_ROWS
> FROM
> (SELECT
> COUNT(*) TOTAL_ROWS
> FROM
> T1) TR,
> T1) TR,
> T1
> WHERE
> TR.POSITION<=(10/TR.TOTAL_ROWS)
> AND TR.ID=T1.ID;

>

> 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

>

> Are you not permitted to use inline views and analytical functions too?
> There must be additional ways to complete this task.

>

> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.

Method #4:
SELECT
  ID,
  FIRSTNAME,
  LASTNAME,
  HEIGHT,
  AGE
FROM
  (SELECT
    ID,
    FIRSTNAME,
    LASTNAME,
    HEIGHT,
    AGE,
    (FIRST_VALUE(HEIGHT) OVER (ORDER BY HEIGHT DESC))*HEIGHT MY_PROD,     MAX_PROD
  FROM
    (SELECT
      MAX(PROD) MAX_PROD
    FROM

      (SELECT
        HEIGHT*LEAD(HEIGHT,9,0) OVER (ORDER BY HEIGHT DESC) PROD
      FROM
        T1)),

    T1)
WHERE
  MY_PROD>=MAX_PROD; Method #5:
SELECT
  ID,
  FIRSTNAME,
  LASTNAME,
  HEIGHT,
  AGE
FROM
  T1
MINUS
SELECT
  ID,
  FIRSTNAME,
  LASTNAME,
  HEIGHT,
  AGE
FROM
  (SELECT
    RANK() OVER (ORDER BY HEIGHT)/COUNT(*) OVER (PARTITION BY 1) ROW_PERCENT,
    (COUNT(*) OVER (PARTITION BY 1)-10)/COUNT(*) OVER (PARTITION BY 1) CUT_OFF,
    ID,
    FIRSTNAME,
    LASTNAME,
    HEIGHT,
    AGE
  FROM
    T1)
WHERE
  ROW_PERCENT<=CUT_OFF;

Method #6:
SELECT
  ID,
  FIRSTNAME,
  LASTNAME,
  HEIGHT,
  AGE
FROM
  (SELECT
    RANK() OVER (ORDER BY HEIGHT)/COUNT(*) OVER (PARTITION BY 1) ROW_PERCENT,
    (COUNT(*) OVER (PARTITION BY 1)-10)/COUNT(*) OVER (PARTITION BY 1) CUT_OFF,
    ID,
    FIRSTNAME,
    LASTNAME,
    HEIGHT,
    AGE
  FROM
    T1)
WHERE
  ROW_PERCENT>CUT_OFF; Method #7:
SELECT

  T1.ID,
  T1.FIRSTNAME,
  T1.LASTNAME,
  T1.HEIGHT,
  T1.AGE

FROM
  (SELECT
    MAX(COUNTER) COUNTER
  FROM
    (SELECT
      LEVEL COUNTER
    FROM
      DUAL
    CONNECT BY
      LEVEL<=10)) C,
 (SELECT
    RANK() OVER (ORDER BY HEIGHT DESC) RANKING,     ID,
    FIRSTNAME,
    LASTNAME,
    HEIGHT,
    AGE
  FROM
    T1) T1
WHERE
  T1.RANKING<=C.COUNTER;

Method #8:
SELECT
  ID,
  FIRSTNAME,
  LASTNAME,
  HEIGHT,
  AGE
FROM
 (SELECT
    ID,
    FIRSTNAME,
    LASTNAME,
    HEIGHT,
    AGE
  FROM
    T1
  ORDER BY
    HEIGHT DESC)
WHERE
  ROWNUM<=10;

I guess the one above is disqualified, since it uses ROWNUM and is reasonably efficient.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc Received on Sat Jan 20 2007 - 07:24:06 CST