Re: Limitting result without ROWNUM

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.

You can use a subquery to find the max(value) where the value selected is not equal to the max(value) on the same table. This will give you the second highest value. If you were to nest the subquery repeatedly you can find the Nth highest value. Then you could just select the rows where size >= the sub-selected 10th value. This is a horrible way to write a query.

Learning assignments with restrictions like this are misguided at best and in my opinion do not promote learning how to solve relational database information retrieval problems efficiently.

HTH -- Mark D Powell -- Received on Fri Jan 19 2007 - 18:24:06 CST