Re: Limitting result without ROWNUM

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 20 Jan 2007 08:06:19 -0800
Message-ID: <1169309179.361445.64680@s34g2000cwa.googlegroups.com>

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)

   );

Method #9:
SELECT

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

FROM
  (SELECT
    ID,
    FIRSTNAME,
    LASTNAME,
    HEIGHT,
    AGE,
    COUNT(*) OVER (ORDER BY HEIGHT DESC) POSITION   FROM
    T1) T1,
  (SELECT
    LEVEL COUNTER
  FROM
    DUAL
  CONNECT BY
    LEVEL<=10) C
WHERE
  T1.POSITION=C.COUNTER; Method #10:
SELECT
  ID,
  FIRSTNAME,
  LASTNAME,
  HEIGHT,
  AGE
FROM
  T1
WHERE
  HEIGHT>
(SELECT
  MAX(HEIGHT)
FROM
  T1
WHERE
  HEIGHT<
(SELECT
   MAX(HEIGHT)
FROM
  T1
WHERE
  HEIGHT<
(SELECT
   MAX(HEIGHT)
FROM
  T1
WHERE
  HEIGHT <
(SELECT
  MAX(HEIGHT)
FROM
  T1
WHERE
  HEIGHT<
(SELECT
  MAX(HEIGHT)
FROM
  T1
WHERE
  HEIGHT <
(SELECT
  MAX(HEIGHT)
FROM
  T1
WHERE
  HEIGHT<
(SELECT
   MAX(HEIGHT)
FROM
  T1
WHERE
  HEIGHT <
(SELECT
  MAX(HEIGHT)
FROM
  T1
WHERE
  HEIGHT<
(SELECT
  MAX(HEIGHT)
FROM
  T1
WHERE
  HEIGHT <
(SELECT
  MAX(HEIGHT)
FROM
  T1
WHERE
  HEIGHT<
(SELECT
   MAX(HEIGHT)
FROM
  T1
)))))))))));

It is starting to look like the instructor asked a very open ended question.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Sat Jan 20 2007 - 10:06:19 CST