Re: Limitting result without ROWNUM

From: Gerard H. Pille <ghp_at_skynet.be>
Date: Sat, 20 Jan 2007 16:00:48 +0100
Message-ID: <45b22d45$0$6467$ba620e4c@news.skynet.be>

Matthias Matker wrote:
> Hello together,
>
> 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.
>

select * from persons
where size >= (

select max(size) from persons c1
where 10 > (select count(*) from persons c2 where c2.size > c1.size) )

I'm trying to select those persons whose height is greater or equal to the height of a person that does not have 10 persons that are bigger.

This may result in more than 10 rows, if the tenth rank has some persons of equal height. Received on Sat Jan 20 2007 - 09:00:48 CST