Re: Limitting result without ROWNUM

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 22 Jan 2007 03:15:59 -0800
Message-ID: <1169464559.751788.150460@q2g2000cwa.googlegroups.com>

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

Nice solution - one that I had not considered. Above is the 11th solution provided to this problem.

Dieter Noeth provided solution #12 (I had to change "t1 AS t2" to "t1 t2" before it would execute)

Method #13 (loosely inspired by the above SQL statement): SELECT

  T1.ID,
  T1.FIRSTNAME,
  T1.LASTNAME,
  T1.HEIGHT,
  T1.AGE

FROM
  T1,
  (SELECT
    T1.HEIGHT,
    COUNT(*) RANKING
  FROM
    T1,
    T1 T2
  WHERE
    T1.HEIGHT<=T2.HEIGHT
  GROUP BY
    T1.HEIGHT
  HAVING
    COUNT(*) BETWEEN 1 AND 10) T2
WHERE
  T1.HEIGHT=T2.HEIGHT; Method #14 (a slight modification of Method #13): SELECT

  T1.ID,
  T1.FIRSTNAME,
  T1.LASTNAME,
  T1.HEIGHT,
  T1.AGE

FROM
  T1,
  (SELECT
    T1.HEIGHT,
    COUNT(*) OVER (PARTITION BY 1) TOTAL_COUNT,     COUNT(*) RANKING
  FROM
    T1,
    T1 T2
  WHERE
    T1.HEIGHT>T2.HEIGHT
  GROUP BY
    T1.HEIGHT) T2
WHERE
  (T2.TOTAL_COUNT-T2.RANKING+1) BETWEEN 1 AND 10   AND T2.HEIGHT=T1.HEIGHT; Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Mon Jan 22 2007 - 05:15:59 CST