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
>
> 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.
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
Received on Sat Jan 20 2007 - 12:11:06 CST
