Re: Limitting result without ROWNUM

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 19 Jan 2007 19:49:18 -0800
Message-ID: <1169264958.905508.173140@s34g2000cwa.googlegroups.com>

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

I agree with Daniel Morgan, and others who responded - ask the instructor, what is the business case for such a request.

Can it be done? Maybe, let's do some setup:

CREATE TABLE T1 (
  ID NUMBER(10),
  FIRSTNAME VARCHAR2(20),
  LASTNAME VARCHAR2(20),
  HEIGHT NUMBER(10,2),
  AGE NUMBER(10));

INSERT INTO T1 VALUES (1,'MARY','SMITH',60,15);
INSERT INTO T1 VALUES (2,'SUSAN','SMITH',65,20);
INSERT INTO T1 VALUES (3,'DOROTHY','SMITH',62,21);
INSERT INTO T1 VALUES (4,'JOHN','SMITH',72,35);
INSERT INTO T1 VALUES (5,'DAVID','SMITH',73,34);
INSERT INTO T1 VALUES (6,'JOSEPH','SMITH',59,13);
INSERT INTO T1 VALUES (7,'ROBERT','SMITH',76,45);
INSERT INTO T1 VALUES (8,'JAMES','SMITH',48,8);

INSERT INTO T1 VALUES (9,'MARY','JOHNSON',60.5,15);
INSERT INTO T1 VALUES (10,'SUSAN','JOHNSON',65.5,20);
INSERT INTO T1 VALUES (11,'DOROTHY','JOHNSON',62.5,21);
INSERT INTO T1 VALUES (12,'JOHN','JOHNSON',72.5,35);
INSERT INTO T1 VALUES (13,'DAVID','JOHNSON',73.5,34);
INSERT INTO T1 VALUES (14,'JOSEPH','JOHNSON',50,13);

INSERT INTO T1 VALUES (15,'ROBERT','JOHNSON',79,45); INSERT INTO T1 VALUES (16,'JAMES','JOHNSON',48.5,8); COMMIT; First experiment:
SELECT
  MIN(HEIGHT) OVER (ORDER BY HEIGHT DESC) MIN_HEIGHT FROM
  T1;

MIN_HEIGHT

---

        79
        76
      73.5
        73
      72.5
        72
      65.5
        65
      62.5
        62
      60.5
        60
        59
        50
      48.5
        48

Interesting, useful?

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. Received on Fri Jan 19 2007 - 21:49:18 CST