Re: Simple Question!

troyt_at_sun.com (troy trimble) wrote:
>
> In article 2904952214560001_at_dscott.is.net, dscott_at_is.net (David Scott) writes:
> >In article <3nio5g$1j7_at_dcsun4.us.oracle.com>, cgohring_at_uk.oracle.com wrote:
> >
> >> tmaxwell_at_crl.com (Anthony B. Cillo) writes:
> >> : I am trying to find the oldest 1000 patients in our database. The
> >> : problem is that I cannot get the system to stop after returning 1000
> >> : rows. I have tried using rownum but that only works if you are not
> >> : using order by.
> >> :
> >> : Any ideas?
> >> :
> >> Use a PL/SQL block
> >>
> >>
> >The solution is easier than you think:
> >
> >CREATE VIEW OLDEST_PATIENTS as
> > SELECT patient_name,age
> > FROM patient_info
> > ORDER BY age DESC
> >/
> >SELECT * from OLDEST_PATIENTS
> > WHERE rownum <= 1000
> >/
>
> If I remember right, you cannot have a WHERE clause in a view or a subquery.
>
> If you're writing a PL/SQL routine to fetch the data, you can define the
> SELECT STATEMENT as a cursor and fetch until the cursor's %ROWCOUNT attribute
> equals 1000.
>
> Later,
> TT
>

No, its that you can't have an Order By in a view ("where" is just fine, thats what views are all about).

Since you can't use rownum like this:

select patient_name, age
from patient_info
where rownum <1000
order by age desc

because the rownum is assigned BEFORE the order by you could try the following query:

• The subquery returns for each record in the outer query
• the number of people OLDER then that record (from the outer query)
• We only keep records in the outer query if the count of
• people older then that record <= 1,000

select patient_name, age
from patient_info a
where 1000 >= ( select count(*)

                from patient_info b
                where b.age > a.age ) 

• The subquery returns 1 row if there are <= 1000 people older
• then the record in the outer query.
• The subquery returns 0 rows if there are > 1000 people older
• then the record in the outer query.

select patient_name, age
from patient_info a
where exists ( select 1

               from patient_info b
               where b.age > a.age
               having count(*) <= 1000 )
               

Even though these queries ask the same question, they generated different plans, you would have to see which one works best for you.

*Note* If you have 1,001 people who are 100 years old AND the maximum age in that table is 100 years old, then you won't ever get a record (cause there are not 1,000 oldest people, there are 1,001 oldest people). In general, since people have the same age, you would never get 1,000 rows from the above but some number of rows <= 1,000. Assume you had 996 people who were 100 years old and 5 people who where 99. The above query would return 996 rows, not 1,001 (the 5 99 year olds would not be returned).

In reality, if you are using PRO*C, I would suggest just

select patient_name, age
from patient_info
order by age desc

And array fetch the first 1,000 rows and stop.

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government Received on Fri May 05 1995 - 00:00:00 CEST