Re: Limitting result without ROWNUM

From: Gerard H. Pille <ghpille_at_hotmail.com>
Date: 22 Jan 2007 00:49:49 -0800
Message-ID: <1169455788.978661.309320@q2g2000cwa.googlegroups.com>

Charles Hooper schreef:
> Gerard H. Pille wrote:
> > 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.

>

> It looks like you supplied another method, but it only returned one row
> when applied to the sample data that I provided. I modified your SQL
> statement as follows to work with my sample data:
> select * from T1
> where HEIGHT >= (
> select max(HEIGHT) from T1 c1
> where 10 > (select count(*) from T1 c2 where c2.HEIGHT >
> c1.HEIGHT)
> );

>

OMG, my bad!

select * from T1
 where height >= (

    select MIN(height) from T1 c1
      where 10 > (select count(*) from T1 c2 where c2.height > c1.height)

    );

I'm trying to select those persons whose height is greater or equal to the height of THE SMALLEST person that does not have 10 persons that are bigger. Received on Mon Jan 22 2007 - 02:49:49 CST