Re: Simple Question!
Date: 1995/05/05
Message-ID: <3odef1$a52_at_dcsun4.us.oracle.com>#1/1
dscott_at_is.net (David Scott) wrote:
>
> 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.
^^^^^^^^^
No, a view cannot use Order By. Select, From, Where, Group by, Having, and UNION
may be used but not Order By.
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
>
> The previous example even allows for multiple people to be the same age.
> Good luck - and check the manuals!
>
> ------------------------------------------------------------------
> David Scott
> ....how can I keep from singing?
> email: dscott_at_is.net
> ------------------------------------------------------------------
Received on Fri May 05 1995 - 00:00:00 CEST