Re: Simple Question!

In article <3o8n84$361_at_theopolis.orl.mmc.com>, troyt_at_sun.com 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.
>
Please review your Concepts Manual, page 5-10; even the very first example of a view contains a WHERE clause! Also check your SQL Language Reference Manual; the syntax explicitly supports WHERE.

Not only does a view support WHERE; it can use GROUP BY, HAVING, and ORDER BY.

The previous example even allows for multiple people to be the same age. Good luck - and check the manuals!